Duplicates Question

[andrewmac]

I have a list of numbers and want to create a helper column which will
place a 1 against unique values but also a 1 against just one of the
duplicate values. Any ideas how I might achieve this would be much
appreciated.#

Many thanks

 

[Dave Peterson]

A formula like:
=countif($a$1:a1,a1)
and dragged down the range

will give you a 1 for the first instance (singletons or first of multiples) of
that value.

The results greater than 1 will be for subsequent duplicates.

 

[andrewmac]

A formula like:
=countif($a$1:a1,a1)
and dragged down the range

will give you a 1 for the first instance (singletons or first of multiples) of
that value.

The results greater than 1 will be for subsequent duplicates.

Thanks Dave but this just gives me the number of instances that each
number occurs against each number. I want something like this:

12345 1
64789 1
35795 1
35795
45128 1

Any other ideas?

 

[Gord Dibben]

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


Gord Dibben MS Excel MVP