Answered

Compare Excel sheet from SharePoint using power automate

(0) Share

Report

Posted on by 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

Categories:

Power Automate Desktop

I have the same question (0)

All responses (4)

Answers (1)

Sort by

abm abm 32,998 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @MathVict2117

Please have a look at this blog

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

Thanks

Was this reply helpful? Yes No
Verified answer

MichaelAnnis 5,727 Moderator on at

Like
a
(1)

Report
Copy link

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

For the bot:

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

For the Excel VBA Macro (#4 above):
Open monthlyusercomparison.xlsx (a pre-made excel spreadsheet with 3 tabs
Open [PriorMonth][Year].xlsx -> Copy A:B -> Paste in A:B of Sheet ("PriorMonth")
Open [CurrentMonth[Year].xlsx -> Copy A:B -> Paste in A:B of Sheet ("CurrentMonth")
Write the following in C2 of Prior Month and Copy Down
=IFERROR(IF(VLOOKUP(A2,CurrentMonth!$A:$A,1,FALSE)=A2,"Stay","Offboarded"),"Offboarded")
Write the following in C2 of Current Month and Copy Down
=IFERROR(IF(VLOOKUP(A2,PriorMonth!$A:$A,1,FALSE)=A2,"Stay","Onboarded"),"Onboarded")
Copy data from PriorMonth tab to the Output tab, Paste in A1
Copy data from Current Month tab, paste in first available row
Filter Data for "Stay" -> Delete Visible Rows
Clear Filter
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

Was this reply helpful? Yes No
MathVict2117 8 on at

Like
a
(0)

Report
Copy link

Link copied!

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

Was this reply helpful? Yes No
MathVict2117 8 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks a lot for your suggestion

Was this reply helpful? Yes No