# Excel - compare 2 lists & highlight differences



## Gram123 (Mar 15, 2001)

Hallo,
I'd like to set up a macro, whereby I can enter two lists of chassis numbers and compare them, so it will identify all of the numbers in list A that do not appear in list B, and all of the numbers in list B that do not appear in list A.

So, my lists may look like:
*List A - List B*
A123 - A123
A124 - A124
A125 - A126
A126 - A127
B555 - B555
B557 - B556
B558 - B559

So, I need to highlight A125 in List A and B556 in List B.
*List A - List B*
A123 - A123
A124 - A124
A125 - A126
A126 - A127
B555 - B555
B557 - B556
B558 - B559

Is there a simple way to do this?

I've been messing around trying to find a way to do this quickly - I tried colouring each list in a different colour and then using the "Unique Records" filter, but it didn't seem to work correctly.

Ta
Gram


----------



## drdrew1469 (Nov 30, 2000)

"Conditional Formatting"

This will do exactly what you want.

Hope that helps,

Drew


----------



## XL Guru (Aug 30, 2003)

Gram, I copied your values to a sheet (A1:B7).

With A1:A7 selected, I used this formula for conditional formatting

=ISNA(MATCH(A1,$B$1:$B$7,0))

For B1:B7, I used

=ISNA(MATCH(B1,$A$1:$A$7,0))

I'm not sure this is what you want as your post is confusing -- you say you want "all of the numbers in list A that do not appear in list B", but you only put A125 in red when as far as I can tell, this criterion also applies to B557 and B558. (?)

HTH anyway,
Andy


----------



## Gram123 (Mar 15, 2001)

Yeah, sorry, my fault.
I was thinking of those lists being just a selection of the full lists.

Yes, that's exactly what I want for highlighting the non-matching records. That will be a massive time-saver!

I've used the cell ranges A1:A10,000 and B1:B10,000 so I know it will always cover the entire list of chassis' in either list. I've used a different conditional formatting colour for either list, so they are distinct. I've recorded the process as a macro, and then assigned it to a button for the user, et voila!

Much appreciated. 

Gram


----------

