find duplicate entries without specifying criteria

[y2k]

can somebody tell me if this is possible. I have a spreadsheet with quiet alot of data. how could I search column1 for duplicates without specifying any criteria. in otherwords, I just want to tell excel, go to column1 and find all the duplicate entries. is this possible?

 

[sen_edp]

Hello

if your column range is A1:A10 GO TO B1 AND ENTER THE FOLLOWING FORMULA

=IF(COUNTIF($A$1:$A$100,A1)>1,"DUPLICATE","")

AND COPY DOWN TO THE END OF THE COLUMN


HTH

_________________
Best Regards,<font color="black"><font size=+1><font color="blue">A<font color="red">ndrea<font color="blue">S</font color="red"><font size=+1><font size=1><font color="red">using xl2000
This message was edited by sen_edp on 2002-09-20 04:57

 

[y2k]

hi andreas,

Thanks for the code. Excel didn't seem to like the ; so I replaced it with a , - is that ok? It found two duplicates, which, as far as I can see aren't duplicates at all. why would this be?

 

[y2k]

hi again,

I sorted my problem. I had put A1 as the cell reference, but rows 1 & 2 were headings, so I should have put A3 instead. once I changed it it worked a treat. thanks again

Martin

 

[sen_edp]

Hello again

see my post above , this is the correct formula.