Unique values in an array.

  • Thread starter Thread starter Jerry
  • Start date Start date
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
 
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).
 
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
Click to expand...



--
 
Back
Top