cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MathVict2117
Frequent Visitor

Compare Excel sheet from SharePoint using power automate

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,

 

MathVict2117_0-1626730227931.png

 

 

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

 

Thanks in advance

 

@sumurthy @Brad_Groux @abm 

1 ACCEPTED SOLUTION

Accepted Solutions
MichaelAnnis
Super User
Super User

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

View solution in original post

4 REPLIES 4
abm
Super User
Super User

Hi @MathVict2117 

 

Please have a look at this blog

 

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

 

Thanks



Did I answer your question? Mark my post as a solution!

If you liked my response, please consider giving it a thumbs up


Proud to be a Flownaut!

Learn more from my blog
Power Automate Video Tutorials
MichaelAnnis
Super User
Super User

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

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

MathVict2117
Frequent Visitor

Thanks a lot for your suggestion 

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events happening this month - don't miss out!

 WHAT’S NEXT AT MICROSOFT IGNITE 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Register for a Free Workshop.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

Users online (2,745)