Compare two lists in two different worksheets and highlight the differences between them

I have two lists on two different worksheets.

Old list is the previous list of names and the New list is an updated list of names.  I want to be able to see what has changed between the two worksheet lists.

I have tried to use COUNTIF but not sure if I am using it correctly because it shows the entire new list as not existing in the old list.

Could someone guide me the right direction?

Sonia BowditchInformation Security OfficerAsked:
Who is Participating?
NorieVBA ExpertCommented:

If you simply want to find if values in one list are in another you can use MATCH.

For example, let's say you have a list in column A on Sheet1 and a list in column A on Sheet2.

You could put this formula in column B on Sheet1,

=IF(ISERROR(MATCH(A1, Sheet2!A:A, 0)), "Not i", "I") & "n list on Sheet2"

and it will tell you which values on the list in Sheet1 appear in the list in Sheet2.
Shaun VermaakTechnical Specialist/DeveloperCommented:
=IF(ISNA(VLOOKUP(A1,Sheet1!A:A,1,FALSE)),"Does not exist","Exists")

Open in new window

To compare changes do this and change 1 to whatever column

Open in new window

Sonia BowditchInformation Security OfficerAuthor Commented:
I have tried VLOOKUP but it keeps coming back as none of the name as existing in the old list.  I have tried TRIM but no luck.
The lists will keep changing with names being added or delete from each list.
Sonia BowditchInformation Security OfficerAuthor Commented:
Vlookup may not work for what I have in mind.  Probably the way to go would be find and match functions or similar.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.