Unique values in an array.

J

Jerry

Is there a way, in Excel 2003, to return a summary list of all the unique
values in an array? For example, in the following array of 10 values, there
are only three unique values. How would I get excel to give me a list of the
unique values?

Array:

BOB
SUE
JON
JON
SUE
BOB
BOB
BOB
JON
JON

I would like Excel to give me a list of the unique values in this array. i.e -

BOB
SUE
JON

Any ideas?

Thanks,
Jerry
 
A

Atishoo

Hi
you can actually filter for unique values use the Advanced command in the
Sort & Filter group on the Data tab, (check unique values box).
 
R

Rodrigo Ferreira

Try this:
Your list is in A1:A13
Put "=A1" in B1
Put
"{=OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B1),R
OW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)}"
in B2
Put
"{=OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B2),R
OW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)}"
in B3
Put
"{=OFFSET($A$1,MATCH(0,MMULT(--TRANSPOSE(TRANSPOSE($A$1:$A$13)=B$1:B3),R
OW(INDIRECT("1:"&ROW()-1))/ROW(INDIRECT("1:"&ROW()-1))),0)-1,0,1,1)}"
in B4
....

Rodrigo

Is there a way, in Excel 2003, to return a summary list of all the
unique values in an array? For example, in the following array of 10
values, there are only three unique values. How would I get excel to
give me a list of the unique values?

Array:

BOB
SUE
JON
JON
SUE
BOB
BOB
BOB
JON
JON

I would like Excel to give me a list of the unique values in this
array. i.e -

BOB
SUE
JON

Any ideas?

Thanks,
Jerry



--
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top