CODEWITHSUNDEEP
sharepointpowerapps
excelguy
excelguy

Reputation: 1624

Reconciliation between Sharepoint List and monthly excel file uploaded to Sharepoint

I am quite new to power apps and trying to determine the best course of action for my below scenario. I want to identify which users submitted to List A and which haven't based on each new monthly file.

I have a List(List A) on Sharepoint I created where users go in and input information, I want to come up with a list of users who have submitted to this list.

I want to reconcile with this monthly file excel that is dropped into a sharepoint link by another user with updated information. So essentially it would be doing a Vlookup into List A to determine who has submitted a record. .

So what I have done:

Created List B manually (from the excel file dropped)

using Power automate/Flow do a vlookup from List B to A whenever a record is created in A.

I created a column in List B that is not within the standard excel file (Column name Submitted, answers would be either Yes/No based on the vlookup) , Vlookup is something like if A.ProjectID = B.ProjectID then B.Submitted = Yes otherwise No.

Problem:

What are my options to get around the fact this file is uploaded monthly? I dont want to recreate List B each month and have to setup the "Submitted" Column each time. I want this to work in an automated fashion.

Some Questions/Ideas:

Is there a way I can overwrite List B and keep the "Submitted" column each month automatically? It is dropped into a new folder each month on SP. ie "Aug 2021" , "Sept 2021", etc.

Can I do the recon in the sharepoint excel file itself automatically each month? Ie When the excel file is created run a macro to create the new column and do a vlookup?

Can a 3rd List be created as some master tracker page to hold all project status'?

Any ideas or suggestions are welcome. Looking for the best way on how to do this. Thanks

Upvotes: 3

Views: 397

Answers (1)

mmikesy90
mmikesy90

Reputation: 981

I think you should approach this from PowerAutomate.

  1. I'd start off by creating a flow with a SharePoint trigger to catch when the Excel file is uploaded.
  2. You should then get the content from the Excel with Excel Online / List Rows in a Table
  3. Use 'Apply to Each' to loop over each user in the table. Inside the loop do the lookup.
  4. Write back the result to the Excel with Excel Online / Update a Row action.

I'd recommend you create a separate thread for the project master list problem and elaborate your goals thereto.

Upvotes: 1

Related Questions