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

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.

On 08/17/2010 07:58, andrewmac wrote:
> 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

On 17 Aug, 14:08, Dave Peterson <peter...@XSPAMverizon.net> wrote:
> 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.
>
> On 08/17/2010 07:58, andrewmac wrote:
>
> > 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

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?

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


Gord Dibben MS Excel MVP


On Tue, 17 Aug 2010 06:18:02 -0700 (PDT), andrewmac <(E-Mail Removed)>
wrote:

>On 17 Aug, 14:08, Dave Peterson <peter...@XSPAMverizon.net> wrote:
>> 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.
>>
>> On 08/17/2010 07:58, andrewmac wrote:
>>
>> > 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
>
>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?
>