Highlight duplicate cells in next column...

**ImpetuousRacer** · 03-21-2008, 08:17 PM

Column A has a list of Entity ID for each customer.
Column B has a list of Entity ID's in multiple instance for how many service appointments they have.

So in Office 2007, I can go to - conditional formatting -highlight cells - equal to. BUT, in only lets me select once cell in Column A to match in Column B.

I need it to look for all the numbers listed in Column A, and Highlight all the matching numbers in column B.

Ive thought about putting the ID's all in one column, and use conditional formatting to find unique, but since some people have two service calls, they dont come back as unique, even though that customer is unique since they dont match my list.

Attached is a sample sheet. The list will be very large so I cant go one by one.

Thanks for the help!

**mikerickson** · 03-21-2008, 08:53 PM

Conditionaly format B2 to the custom formula

=ISNUMBER(MATCH(B2,$A:$A,0))

copy this to the rest of column B and every entry in column B that is also in column A will be highlighted.

**ImpetuousRacer** · 03-24-2008, 02:03 PM

Thanks! This worked great, BUT, I wanted to use it so I could right click on the Cell, go to Sort, then "Put Selected Cell Color on Top".

This way I could delete all the non-highlighted entries. However, this option isnt working? Any ideas?

**mikerickson** · 03-24-2008, 03:02 PM

Color that is the result of Conditional formatting doesn't show up the way that normal coloring of a cell does (I'm not familiar with the quirks of 2007 color sorting.)

What rows do you want to delete? A macro could be written to do that directly without the detour into coloring cells.

**ImpetuousRacer** · 03-24-2008, 04:28 PM

Ok. I have a revised list which should make things easier. This list is before I used a count to see how many times a client was in for service.

I just need to keep all the people that match column A, and delete all the people that dont match.

Attached is a new sample. Thanks for the help on this.

**mikerickson** · 03-24-2008, 08:41 PM

I think this will do it.

Please Login or Register  to view this content.

See attached.

**ImpetuousRacer** · 03-25-2008, 03:24 PM

This seems to work perfectly! I will keep you updated if I run into any hitches. Thank you so much! I wish there was some knowledge I could offer you. Thanks again.