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/7...iquevalues.png

Thanks, Mike

Take a look at this from Chip Pearson's website...
http://www.cpearson.com/Excel/DistinctValues.aspx
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"מיכ�ל (מיקי) �בידן ®" wrote:

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/7...iquevalues.png

Thanks, Mike

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

"Gary Brown" wrote:

Take a look at this from Chip Pearson's website...
http://www.cpearson.com/Excel/DistinctValues.aspx
--
Hope this helps.
If it does, please click the Yes button.
Thanks in advance for your feedback.
Gary Brown



"מיכ�ל (מיקי) �בידן ®" wrote:

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/7...iquevalues.png

Thanks, Mike

מיכ�ל (מיקי) �בידן ® micky-a*at*tapuz.co.il wrote...
....
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.

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

"Herbert Seidenberg" wrote:

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, Harlan,
Great solution.
Mike


"Harlan Grove" wrote:

מיכ�ל (מיקי) �בידן ® micky-a*at*tapuz.co.il wrote...
....
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.

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.


"Herbert Seidenberg" wrote:

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