Compare several excel workbooks

[David Yeates]

I need to compare approximately 8 excel workbooks for differences.

I have come across the following:

https://support.office.com/en-us/article/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842?ocms...

However, it is beyond my comprehension, and concerned that I will mess it up.

Is there anyone able to assist for a small fee?

[#2]

Hi David,

The link you mentioned isn't related to what you asking for!

You may check out this link instead.

Hope that helps

[#3]

What -preciesly- needs to be compared between these 8 files? Data, Formulas, other things?

[#4]

Thank you for that Haytham. It was interesting to read it but it only offers advice on comparing two excel worksheets. I have multiple sheets I wish to compare.

[#5]

Hi Jan,

I wish to compare a row with two columns, one containing a name of a person, the second containing their e-mail address.

Indeed, merely comparing e-mail addresses would be enough.

[#6]

Is this a one-off thing? If yes I would simply copy/paste the data into one single sheet with an additional column for the filename. You could also use Data, New Query, From File, From Folder and go from there.

[#7]

Thank you Jan. Yes it is a 'one-off' thing. Basically I am trying to compare data (names) from multiple sheets.

I have done what you recommend, which is to copy and paste the names into a single sheet. I now have 6 columns of names and I now need identify which repeat from in every columns (although they are not necessarily also on the same row in each column) or to identify those names that do not repeat. Not sure that 'Conditional Formatting' and 'Duplicate Values' does it correctly.

[#8]

The conditional formatting should work just fine (highlight duplicates)..

[#9]

Thank you Jan. One final question, promise. The list is a very long one - around 15,000 names, so it is having a problem coping with it. I presume I just need to be patient.

However, what I want to make sure of is that the duplicates are only identified if the name appears in all of the columns. Can you confirm that this is what the Conditional Formatting analysis provides. If the name was in 5 out of the 6 columns, I would not wish it to be highlighted.

[#10]

No it doesn't do that, it highlights duplicates wherever they are found, in 2, 3 or 10 places. In that case a formula would probably be better.
- Make sure you use the longest list to put the formula next to.
- Suppose this is column A
- Suppose the other lists are in columns C, F, I, L, O, R, T, W
- Enter this formula in any column :
=IF(ISERROR(MATCH(A2,C2:C1000,0)+MATCH(A2,F2:F1000,0)+MATCH(A2,I2:I1000,0)+MATCH(A2,L2:L1000,0)+MATCH(A2,O2:O1000,0)+MATCH(A2,R2:R1000,0)+MATCH(A2,T2:T1000,0)+MATCH(A2,W2:W1000,0)),"Not In All","In All")

[#11]

It works:) Many thanks Jan.

[#12]

You're welcome!