Finding Duplicates in a Column (Without Sorting)

[ConfusedNHouston]

I am pulling data into a LARGE spreadsheet and that data is coming from 4
separate sites. A product might well be found in sites A, C and D. Others
might be found only in A. A sort would obviously group the duplicates, but a
sort is not practical. There are over 45,000 rows of data in the spreadsheet
and finding dupes "by-eye" would drive me to madness.

Is there a way to have Excel "look" down column A and find any item name
(alpha-numeric format) that is repeated. Is there a way to segregate or
highlight the duplicates; or to produce a list of the duplicates? The idea
thing would be if it could copy the duplicating records to a separate
worksheet. I'll take what I can get.... Thanks.

 

[Dave F]

See here for some ideas: http://www.cpearson.com/excel/duplicat.htm

Dave

 

[Joel]

You need to write a macro
First Sort Data, then compare data in rowcount witth rowcount + 1.
If the data matches, then copy to new worksheet

rowcount = 1
dup_rowcount = 1
do while cell(rowcount,"A") <> ""
if cell(rowcount, "A") = cell(rowcount + 1, "A") then

worksheet("duplicates").cell(duplicaterowcount, "A") = _
range(cell(rowcount, "A"),cell(rowcount,"D")
dup_rowcount = dup_rowcount + 1
endif

rowcount = rowcount + 1
loop

 

[Dave Peterson]

Chip Pearson has lots of techniques to work with duplicates:
http://www.cpearson.com/excel/duplicat.htm