Locating duplicate numbers in a column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Can someone tell me how to search for duplicate numbers in a column of an EXCEL 2000 spreadsheet. I have been doing this manually, but it is teadious with long lists (5000+).
 
David

Use a helper column alongside your data. In the next empty column (say D2)
try
=COUNTIF(A:A,A2)
This will give you a count of how many cells identical to A2 there are in
column A. You can then autofill this down.

Hope this helps.

Andy.

David said:
Can someone tell me how to search for duplicate numbers in a column of an
Click to expand...
EXCEL 2000 spreadsheet. I have been doing this manually, but it is teadious
with long lists (5000+).
 
One way of doing it is to create a copy of the worksheet for data manipulation. Then sort the dara array by the column that contains the duplicates by ascending order. Assuming the data is in column B, write a formula in column C to compare the adjacent cells in column B to identify the duplicates. A formula can be written in cell C2 as =if(B2=B1,"Duplicate",0). This formula can then be copied and pasted to reach the end of data array in column B
Regards
J.C.
 
if you want to find the second occurances you can use conditional
formating to highlight everything after the first occurance

for the condition use formula is and enter
=COUNTIF(A$1:A1,A1)>1 then copy the formatting to all cells in the
column

you will now have all repeat entries highlighted


Randall
 
Back
Top