Duplicate Data Spotting

[Joneye]

I have a data sheet Colum A1 to A1000 has 1000 items in it, im sure some have been entered in twice how can I spot this with a formula?

regards

Jon

 

[Richard Schollar]

Hi Jon

In B1 copied down:

=COUNTIF($A$1:$A1,$A1)

duplicates will be those formulas returning a result >1

 

[Joneye]

Hi,

Sadly this does not work, I have copied the formula and have put in 3 duplicates to test, not a duplicate gets listed as a number 1 and duplicates get listed as a 2 if they are a cell apart (i.e. on ontop of the other) if not they are represented by a 0.

Am i going wrong somewere?

Im using excel 2003, the data is A:1 to A:1000 it can be located anywere in this stacking...

 

[Jon von der Heyden]

Hello

This methd works. Note the anchor on the first A1 ($A$1):

Excel Workbook
	A	B
1	Value1	1
2	Value2	1
3	Value3	1
4	Value3	2
5	Value3	3
6	Value2	2
7	Value1	2
8	Value1	3
Sheet1

Excel 2007

Cell Formulas
Range	Formula
B1	=COUNTIF($A$1:A1,A1)

All results greater than 1 are duplicates.

 

[Michael M]

Hey Jon
try

=COUNTIF(A1:A1000,$A1)

 

[Joneye]

worked a GEM many thanks.