finding duplicate records

  • Thread starter Thread starter fazlici
  • Start date Start date
F

fazlici

I have a .LIS document with about 5000 names and I would like to find
all the duplicates and display only them. I have tried filtering as
well as conditional formatting but haven't got what I wanted. All help
is much appreciated.
 
Assuming you data starts in cell A2, put the following formula in another cell in row 2:

=IF(COUNTIF($A$2:A2,A2)=1,1,0)

Copy or fill down to the bottom of you data. If the formula is in a column directly to the right of your data you can just double click on the fill handle(black square in lower right corner of selected cell).

This will return a 1 for every unique record and a zero for all duplicates. Assuming the IF functions are in column B, then =SUM(B:B) will give you the total.

Good Luck,
Mark Graesser
(e-mail address removed)


----- fazlici wrote: -----

I have a .LIS document with about 5000 names and I would like to find
all the duplicates and display only them. I have tried filtering as
well as conditional formatting but haven't got what I wanted. All help
is much appreciated.
 
Is it an XL file? I am not familiar with the LIS extension. Anyway, if it is
an XL file, select all the data and Format>Conditional Formatting>Formula
IS>=COUNTIF($A$2:$A$5001,A2)>1)
If it is not an XL file, you may be able to do something in the application
that creates an .LIS file. Change the cell references to meet your needs.
 
Back
Top