topic Re: MS Excel 2000: Highlighting duplicate records in Operating System - Microsoft

MS Excel 2000: Highlighting duplicate records

Sanjay Verma — Tue, 17 Sep 2002 22:15:57 GMT

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

Re: MS Excel 2000: Highlighting duplicate records

Ron Kinner — Tue, 17 Sep 2002 23:23:47 GMT

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

Re: MS Excel 2000: Highlighting duplicate records

Sanjay Verma — Tue, 17 Sep 2002 23:26:18 GMT

Hi Ron,

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

Re: MS Excel 2000: Highlighting duplicate records

Ron Kinner — Wed, 18 Sep 2002 18:58:11 GMT

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

Re: MS Excel 2000: Highlighting duplicate records

Patrick DeKenipp — Fri, 08 Nov 2002 02:26:48 GMT

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