Locating duplicate numbers in a column

[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+).

 

[Andy B]

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.

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+).

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/duplicat.htm#TaggingDuplicates
http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

 

[Guest]

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.

 

[Randall Roberts]

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