Compare Excel sheet from SharePoint using power automate

[MathVict2117]

Hello Experts!

Please pour your suggestions on how to create a power automate  flow for below requirement i tried with couple of ways like compare and highlight the differences and ended up with issues.

Requirement:

Every month end we will get an excel sheet which contains list of users from a project ,every month new users will be onboarded and off boarded. I need to create a flow which compare the current month data(July2021.xlsx) and previous month data(June2021.xlsx)

and  find the differences of two excel sheets and find how many new users has been onboarded and how many existing users are offboarded and need to show the differences in new excel sheet (output_diff.xlsx)

Expected output as below,

Kindly help to suggest some ways or ideas to implement this using power automate flow

Thanks in advance

@sumurthy @Brad_Groux @abm 

[abm]

Hi @MathVict2117 

Please have a look at this blog

Compare 2 Excel files using Power Automate (sharepains.com)

Thanks

[MichaelAnnis · Accepted solution]

Any Excel work is best done using VBA.  Then, the bot can open Excel, run the VBA, and produce an output.

For the bot:

1. Get your variables
   
   1. Prior Month
   2. Current Month
   3. Year
2. Download the files from sharepointe
3. Open your Bot Macro Workbook
4. Run the Macro for monthly user comparison (see below).
5. Upload the output file to sharepointe

For the Excel VBA Macro (#4 above):

1. Open monthlyusercomparison.xlsx (a pre-made excel spreadsheet with 3 tabs
2. Open [PriorMonth][Year].xlsx -> Copy A:B -> Paste in A:B of Sheet ("PriorMonth")
3. Open [CurrentMonth[Year].xlsx -> Copy A:B -> Paste in A:B of Sheet ("CurrentMonth")
4. Write the following in C2 of Prior Month and Copy Down
   1. =IFERROR(IF(VLOOKUP(A2,CurrentMonth!$A:$A,1,FALSE)=A2,"Stay","Offboarded"),"Offboarded")
5. Write the following in C2 of Current Month and Copy Down
   1. =IFERROR(IF(VLOOKUP(A2,PriorMonth!$A:$A,1,FALSE)=A2,"Stay","Onboarded"),"Onboarded")
6. Copy data from PriorMonth tab to the Output tab, Paste in A1
7. Copy data from Current Month tab, paste in first available row
8. Filter Data for "Stay" -> Delete Visible Rows
9. Clear Filter
10. Save Output tab as "output_diff.xlsx" where needed

This will work...if you don't know VBA, the Record Macro option should be good enough, and anything that is not working you should be able to troubleshoot through googling.

Good luck

[MathVict2117]

Thanks a lot let me give a try and update you asap

[MathVict2117]

Thanks a lot for your suggestion