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
Impactful Individual
Impactful Individual

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 III
Super User III

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 blogPower Automate Video Tutorials
MichaelAnnis
Impactful Individual
Impactful Individual

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

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

MathVict2117
Frequent Visitor

Thanks a lot for your suggestion 

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,466)