Need function for finding and indicating repeats/duplicates

**CL39** · 01-20-2008, 01:00 AM

I have a data of ten columns and 1000+ rows.
Column A has the date and column D has the ID#.

Need to find a way to do the following.

If a ID# is repeated for a given date, then keep the first row
with that ID# but delete the other
rows that contain the same ID# in Column D or,
if not delete then at least indicate those rows with some
change in appearance (say, highlight them, or bold type)

Thank You

**Max, Singapore** · 01-20-2008, 02:18 AM

One way, use an empty col to the right (eg col K) to flag the duplicate lines, then autofilter on col K for the dups and delete.

Place in say, K2:
=IF(COUNTA(A2,D2)<2,"",IF(SUMPRODUCT((A$2:A2=A2)*(D$2:D2=D2))>1,"dup",""))
Copy K2 down to the last row of data. Apply autofilter on col K, filter out: dup. These are the duplicate lines you seek. Select all the filtered lines, right-click > Delete Row, then remove autofilter.