Compare and Identify Differences

**sswood101** · 09-07-2010, 01:19 PM

It's been awhile since I've come here for help but you all are the experts who get it done. I don't know what I need to do to get Excel to compare two worksheets and identify the differences. I want to compare names between 2 worksheets and identify the names that are not on both sheets. I'm by no means an expert so any formula's you can provide would be tremendously appreciated.

**NBVC** · 09-07-2010, 01:22 PM

something like this.

In Sheet1...

=ISnumber(Match(A1,'Sheet2'!A:A,0))

where A1 is first piece of data in Sheet 1 to look for.

Sheet2!A:A is column A in Sheet2, where you want to search for a match.

Result of TRUE means a match was found... FALSE means no match found.

Copy formula down.

**sswood101** · 09-07-2010, 01:47 PM

Thank you, NBVC. So this formula matches row 1 in sheet 1 against row 1 in sheet 2? What if the name on sheet 1, row 1, is found on sheet 2, row 29? And it only confirms in one direction (in other words, if Tom Jones is on sheet 1 but not on sheet 2 then I would get a false response, but if he was in sheet 2 and not in sheet 1... he wouldn't be identified?)

**NBVC** · 09-07-2010, 02:11 PM

No it matches Row 1 in Sheet 1 to column 1 in Sheet2, so if a match is found anywhere in Column A of Sheet2 you get TRUE.

**sswood101** · 09-07-2010, 02:14 PM

Yes, you are right. It does work that way. Thank you. If I understand, now I use the same formula in sheet 2, revised to fit, to check sheet 1?

**NBVC** · 09-07-2010, 02:21 PM

Yup, that's right.

**sswood101** · 09-07-2010, 03:09 PM

NBVC, you rock. Thank you for all the help. I have to poke around here to remember how to mark this resolved. Thanks, again!

**NBVC** · 09-07-2010, 03:11 PM

In case you can't find it:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save