Formula - Analyse range, return unique value

[S Davis]

Hi there,

I used to have a formula for this, but a computer swap has left me
without any of my saved excel formulas. If someone can help me out, I
would appreciate it.

I need a formula where it can essentially look at a range,
dynamically, and then return the unique values within that range. This
formula will be present beside a query being refreshed from our
server, so it cannot be a 'drag down' type formula and must coincide
to its row number. But perhaps Im asking for too much - I'll take what
I can get.

Essentially, return the value only if the data is being returned the
first time:

Data - - - Returned
101 - - - 101
102 - - - 102
101 - - -
103 - - - 103
104 - - - 104
102 - - -
101
105 - - - 105

Any help is greatly appreciated!!! I'm starting my furious searching
(this has already gone off to a VP with an error... woops) and will
report back with any findings - in the meantime, any knowledge that
can be shared would be great

 

[S Davis]

Hi there,

I used to have a formula for this, but a computer swap has left me
without any of my saved excel formulas. If someone can help me out, I
would appreciate it.

I need a formula where it can essentially look at a range,
dynamically, and then return the unique values within that range. This
formula will be present beside a query being refreshed from our
server, so it cannot be a 'drag down' type formula and must coincide
to its row number. But perhaps Im asking for too much - I'll take what
I can get.

Essentially, return the value only if the data is being returned the
first time:

Data - - - Returned
101 - - - 101
102 - - - 102
101 - - -
103 - - - 103
104 - - - 104
102 - - -
101
105 - - - 105

Any help is greatly appreciated!!! I'm starting my furious searching
(this has already gone off to a VP with an error... woops) and will
report back with any findings - in the meantime, any knowledge that
can be shared would be great

Also totally acceptable would be the following:

Data - - - Returned
101 - - - 1
102 - - - 1
101 - - - 2
103 - - - 1
104 - - - 1
102 - - - 2
101 - - - 3
105 - - - 1

.... which I could then just write a formula to read only '1's.

Purpose of this list is to count the number of entities in a list, but
some of them have multiple entries.

Thanks again,
-SD

 

[Guest]

See

http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique

HTH

 

[T. Valko]

Purpose of this list is to count the number of entities in a list,

but some of them have multiple entries.

If you want the count of unique entries try one of these:

Since your sample data is numeric:

=COUNT(1/FREQUENCY(A1:A10,A1:A10))

This one is a generic formula for counting uniques:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

Biff

 

[S Davis]

See

http://www.cpearson.com/excel/duplicat.htm#ExtractingUnique

HTH










- Show quoted text -

This worked perfectly, thank you !