MS Excel 2000: Highlighting duplicate records

[Sanjay Verma]

Hi Friends,

I want to highlight or color duplicate records in a column. How I can do this?

The alternate option is using "Advanced Auto-Filtering" but it moves the contents to other cells or hides. I don't want to hide or move the contents but just highlight / color duplicate fields or cells or values.

An early response on this will be appreciated.

Thanks & Regards,
Sanjay

[Ron Kinner · Accepted solution]

A bit awkward but if you can record it as a macro it shouldn't be too bad:

Highlight your data,

Format/Cells/Font/Color/Red

Data/Filter/Advanced Filter, Check => Filter the list, in place,
Check => Unique records only

OK

Format/Cells/Font/Color/Black

Data/Filter/Show All

Ron

[Sanjay Verma]

Hi Ron,

I've several hundreds/thousands of records in the spreadsheet. How am I going to know which ones are duplicate?
~Sanjay

[Ron Kinner]

After going through my little routine the extra copies are in red. Don't think it cares how many entries there are - it's not like you have to do it to each entry. You can probably just highlight the whole page tho I didn't test that. Record the process as a macro and it should be pretty simple and quick.

I'll admit it doesn't highlight both copies but if you do a sort you would have the original and its copy together. What do you want to do with the copies besides make them a different color?

Ron

[Patrick DeKenipp]

Hi Sanjay,

I have developed an Excel VBA macro that does exactly what you are looking for. You select which column has the duplicate records, then select whether to highlight or delete the duplicate records. Contact me if you are interested.

Patrick DeKenipp
pdekenipp@hotmail.com