Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique values in an array.
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique values in an array.
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). "Jerry" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Unique values in an array.
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 Jerry wrote: 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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning an array of unique values? | Excel Worksheet Functions | |||
Array formula for unique values | Excel Worksheet Functions | |||
Array formula for unique values | Excel Worksheet Functions | |||
Non-unique elements in an array | Excel Discussion (Misc queries) | |||
create list of unique values from a column with repeated values? | Excel Worksheet Functions |