Workbook Comparison

Copper Contributor

I need help, as I have exhausted all the options I know.


Workbook A is from Oct.

Workbook B is from Nov.

Each workbook is data exported from our EHR system, and I could not choose how it was exported. Each workbook contains a total of between 800,000-1,000,000 lines of information within multiple worksheets. 


we are trying to find the 200,000 lines of discrepancies. I cannot figure out how to do this.


The Workbook Compare all doesn’t give me the options I need. I have document names, with the folder names they’re saved in and patient names associated with: I need to find out what is missing between the two. 

I have tried so many formulas but none of them seem to be working since I don’t have either set information to compare against or since I am simply needing the missing info. Any formula I use, somehow I am hitting a road block as to why it won’t work. Please help 

7 Replies



You don't provide any examples of the data, so it is difficult to provide help.


The free 'Professional_Compare' workbook is worth a try.

It makes a cell to cell comparison between two worksheets,


makes a row comparison between two worksheets.

Your should try the row comparison.

It is easy to use.


Download from OneDrive...!Au8Lyt79SOuhZw2MCH7_7MuLj04?e=sAwbHU



Nothing Left To Lose

@Nothing_Left_to_Lose  Being the type of Data I have, I cannot share it as it is private information. I did, however mention that it was document titles, with the name of the folder they were saved in, and the patient name. Those are the 3 pertinent fields of interest. If you could tell me what else you would need to give examples I could tell you.


If you're using an enterprise version of M365 (through an employer), you may have access to Inquire's Compare Files:



More details:

Compare workbooks using Spreadsheet Inquire - Microsoft Support

I am! Thank you I will check this right now.
I tried this and it doesn't give the desired results. I need to know what Data is MISSING from Workbook B that is on Workbook A. When I look at the options they don't answer that for me. Entered Values is only telling me data that was changed. Calculated values is only the values calculated from formulas I put in. Then there is structural etc. None of it returns what I need.




If you can copy and paste the new set of multiple worksheets into a new single worksheet and do the same for the previous set of worksheets, then the 'Professional_Compare' workbook might be able to do the job.


What I don't understand is why/how the "name of the folder they were saved in" is included as a pertinent column title?



Nothing Left To Lose



Hey there, due to the information I am working with, and why we are running this comparison, all 3 columns of data is pertinent. I need to know all 3 items together (in a row = a match) and then if it is present on both Workbook A and B.

I have tried copy/pasting but it is actually 1,000,000 lines of data and to do that continues to crash both excel and the computer. Even after adding more RAM to our computer. It is a massive amount of data.