Convert a 8X3 table to a vertical range of the Unique strings only

[מיכ×ל (מיקי) ×בידן ®]

Hi,

I have got a 2 dim. table (8 rows by 3 columns).

24 strings (2-3 characters each) were typed into the table - however only 8
of them are unique - meaning, some of them appear more than once.

I'm looking for a Worksheet Function in order to present only the unique
strings in adjacent(!) cells in a vertical range in col. A

If the results in the vertical range could be sorted, in ascending order,
will be fine.

(I know how to achieve that with 1-2 helper columns but I prefer solving it
without those helpers).

With your permission I uploaded a picture to
in order to emphasize what I have in mind.

http://img299.imageshack.us/img299/7765/tabletouniquevalues.png

Thanks, Mike

 

[Gary Brown]

Take a look at this from Chip Pearson's website...
http://www.cpearson.com/Excel/DistinctValues.aspx

 

[מיכ×ל (מיקי) ×בידן ®]

Thanks for your efforts.
Unfortunately, the suggested UDF does not meet my request.
Mr. pearson stated very clearly that (quote): "Two-dimensional ranges are
not supported".
Other suggestions will be appreciated.
Mike

 

[Harlan Grove]

I'm looking for a Worksheet Function in order to present only the unique
strings in adjacent(!) cells in a vertical range in col. A

If the results in the vertical range could be sorted, in ascending order,
will be fine.

....

If the 8 by 3 table were named T and the first result were in cell E1,

E1:
=INDEX(T,MATCH(2,1/MMULT(-(COUNTIF(T,"<"&T)=0),{1;1;1})),
MATCH(0,INDEX(COUNTIF(T,"<"&T),
MATCH(2,1/MMULT(-(COUNTIF(T,"<"&T)=0),{1;1;1})),0),0))

E2:
=INDEX(T,MATCH(2,1/MMULT(-(COUNTIF(T,"<"&T)=COUNTIF(T,"<="&E1)),
{1;1;1})),
MATCH(COUNTIF(T,"<="&E1),INDEX(COUNTIF(T,"<"&T),
MATCH(2,1/MMULT(-(COUNTIF(T,"<"&T)=COUNTIF(T,"<="&E1)),{1;1;1})),0),
0))

Fill E2 down as far as needed.

 

[Herbert Seidenberg]

Excel 2007
Converts any size table.
Uses no formulas.
Dynamic.
Can be turned into a Macro or UDF.
http://www.mediafire.com/file/gz53tygeznz/04_30_09.xlsx

 

[מיכ×ל (מיקי) ×בידן ®]

Thanks, Herbert,
Can this be achieved, that way, in Excel versions prior "2007" !?
Mike

 

[מיכ×ל (מיקי) ×בידן ®]

Thanks, Harlan,
Great solution.
Mike

....

If the 8 by 3 table were named T and the first result were in cell E1,

E1:
=INDEX(T,MATCH(2,1/MMULT(-(COUNTIF(T,"<"&T)=0),{1;1;1})),
MATCH(0,INDEX(COUNTIF(T,"<"&T),
MATCH(2,1/MMULT(-(COUNTIF(T,"<"&T)=0),{1;1;1})),0),0))

E2:
=INDEX(T,MATCH(2,1/MMULT(-(COUNTIF(T,"<"&T)=COUNTIF(T,"<="&E1)),
{1;1;1})),
MATCH(COUNTIF(T,"<="&E1),INDEX(COUNTIF(T,"<"&T),
MATCH(2,1/MMULT(-(COUNTIF(T,"<"&T)=COUNTIF(T,"<="&E1)),{1;1;1})),0),
0))

Fill E2 down as far as needed.

 

[Herbert Seidenberg]

Excel 2003
Since "Remove Duplicates" is not featured in 2003,
this macro implementation is clunky:
http://www.mediafire.com/file/yzmlmlnlmjk/04_30_09.xls

 

[מיכ×ל (מיקי) ×בידן ®]

Thanks, Herbert.