How to compare data in 2 Excel sheets

[Mike49]

Hello 🖐,

I have 2 Excel sheets which contain similar data i.e same column headings. Each month I have to go through both sheets to compare the reference number columns to make sure the reference numbers contained in sheet 1 are the same in sheet 2, if there are any which are different i.e do not match then I would highlight/record those reference numbers on the sheet.

Is there a connector available for this which can compare both excel files and any advice on this will be highly appreciated. 

Many thanks in advance!

[wskinnermctc]

Can you use the Spreadsheet Compare tool?

https://support.microsoft.com/en-us/office/basic-tasks-in-spreadsheet-compare-f2b20af8-a6d3-4780-801... 

[spmofulton]

@wskinnermctc I think what he/she is asking for is like the power automation of spreadsheet compare.

Given the landscape of database version control, I honestly thing it would be a good thing to have as a feature in either power automate or one drive excel in general.   Many alternatives are already automate-able

@Mike49  I am hoping that someone gives you a reply that tells you how to automate spreadsheet compare as that is exactly what you need.   However, power automate cannot automate spreadsheet compare then I can for this specific case suggest alternatives.

• If you can convert the files to csv files then you can use a private GitHub repository to regularly find differences between to database tables.  Github is a Microsoft product.   You just have to enable github large file storage.   You can then script an automated update code.  
• Alternatively if your data is always less than .5 million rows then you can use google sheets for free to check.  Actually what the version control/history button does.  You just reupload your new excel sheet to replace the old excel sheet.   You can then google's built in sync apps to automate.  
• Tableau also allows this for free with Tableau public and a more in-depth statistical using Tableau Prep. You use load in the excel files and stack them on top of each other through a union. 
• Finally both R and python allow you to do this for free in about 5 lines of code or less.  Its more a coded version but let me know, and I can share my organization code that does this. 

I am unironically hope there is a power automation solution for spreadsheet compare as that would be really cool though. 

[wskinnermctc]

Can you give screenshots of the excel workbooks with the column headings and what you mean by reference number columns?

So I need to see examples of two workbook tables you would be comparing and the data that is similar and different.

Depending on what the data is that you are trying to compare, it might be possible to put them all in an array and see which one is missing something.

It might take some data gymnastics, but just have to see what you are working with. 

Is this supposed to be automated somehow? Or do you plan on manually selecting each file in the flow and push button run the flow everytime you want to compare the sheets?

[Anonymous]

Hi wskinnermctc,

Please see the examples I have attached from both spreadsheets:

Ideally I would like a push button flow where I manually put the files in a folder and hit the push button which will compare the files and generate the results (if possible). Thanks!

[Anonymous]

Hi spmofulton,

Thanks for the alternative options. Will Google sheets version control button generate a new file? I would not want the old file replaced but I would like to see any reference numbers which do not match in sheet A and B. It would be good if a new file is then created to show the numbers/rows that do not match or atleast highlighted to show the unmatching numbers.

The Python code which you have mentioned; does it go into Microsoft automate app or Tableau? it will be helpful if you can provide that, I can run a test on it. Thanks!

[wskinnermctc]

Comparing spreadsheets with power automate push button flow seems like Spreadsheet Compare with extra steps.

[David_MA]

I am currently working on something similar, which is for updating an Outlook distribution list. It is for an export of people managers to see who needs to be added/removed each month as people come and go. I reviewed several solutions I found online and pooled and adapted them to my needs to generate the following which requires two workflows. However, I am sure they could be combined into one. One flow does a check to see who/what needs to be added, and the other flow checks to see who/what needs to be removed.

• My two spreadsheets are book1.xlsx and book2.xls. The first one, book1.xlsx will always contain the current month's export, which we are going to do through an RPA process. Before the RPA process gets the current items from SAP, it will take the existing data from book1.xlsx and replace the data in book2.xlsx, thus book2.xlsx will always contain the prior month's data.
• My first flow compares the items in book1.xlsx to book2.xlsx to find who has been added since the prior month to see who needs to be added to the distribution list. The following are the actions from that flow (it will eventually be a scheduled flow rather than manually triggered:

• The to initialize variables actions initialize one variable named Found and one Not Found and both are arrays.
• The two select actions after the list rows present in the two spreadsheets get the field I want to compare from the current and previous month:
  
• In the above, the expression is just taking the e-mail address column in the spreadsheets and making it lowercase since the comparison is case sensitive just in case for some reason the case changes. Since I know the e-mail address in AD need to be unique, I am not worried about this.
• In the Apply to each action, we're going to compare the Select from Book1 to the Select from Book2:
  

  In the above, the first output in the Apply to each is the Output of Select Book 1 and in the condition, the Output is that of Select Book 2. If it returns yes, it adds the e-mail address to the Found array variable and if not, it adds the e-mail address to the Not Found array variable.

• Next, I use two compose actions to pass the data from both the Found and Not Found arrays. I don't really need the Found one for what I am doing, but since this is still in development is makes it easy to do a check.
• If you have done all of this, you will want to run your flow to make sure you have no errors before you add the Parse JSON action. You will need to go into your successful run and copy what is returned from the Compose Not Found action so you can set up the Parse JSON action:
  

  When you have a successful run up to this point, as I said you can copy the output from the Compose Not Found action and click Generate from sample to configure the Parse JSON action.

• I then take the body of the Parse JSON in a Select action and then create an HTML table so the results can be e-mailed to our IT department which manages the distribution lists:
  
• The condition just checks to make sure there were items not found. If so, it sends the e-mail to IT otherwise it notifies me that nobody needed to be added this month.
  
• To find out who or what needs to be removed, just make a copy of the workflow and you just need to make two changes to the apply to each action:
  
  In the above you just swap the two outputs of the Select Book 1 and Select Book 2 actions. So, in the first part of the Apply to each you use the Output from Select Book 2 and in the condition, you use the Output of the Select Book 1 action.

Again, I am still in development on this, but my initial testing is showing it works. I am not sure how I would highlight the differences in Excel like you want to do, but I am sure you could take the Not Found variable and use it in an apply to each action to update the rows in Excel.

Good luck!

[David_MA]

P.S. To make the flow run faster, you can turn on concurrency in the Apply to each actions:

I am processing about 200 rows of data in each spreadsheet and the flow completes in about 35 seconds in testing.