Formula - Analyse range, return unique value
 

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 :)

Formula - Analyse range, return unique value
 

On Feb 20, 12:28 pm, "S Davis" wrote:
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

Formula - Analyse range, return unique value
 

See

http://www.cpearson.com/excel/duplic...tractingUnique

HTH

"S Davis" wrote:

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 :)

Formula - Analyse range, return unique value
 

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" wrote in message
oups.com...
On Feb 20, 12:28 pm, "S Davis" wrote:
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

Formula - Analyse range, return unique value
 

On Feb 20, 12:55 pm, Toppers
wrote:
See

http://www.cpearson.com/excel/duplic...tractingUnique

HTH



"S Davis" wrote:
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 :)- Hide quoted text -

- Show quoted text -

This worked perfectly, thank you !