If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Convert a 8X3 table to a vertical range of the Unique strings only
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 |
#3
|
|||
|
|||
Convert a 8X3 table to a vertical range of the Unique strings
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 |
#4
|
|||
|
|||
Convert a 8X3 table to a vertical range of the Unique stringsonly
מיכאל (מיקי) אבידן ® 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. |
#5
|
|||
|
|||
Convert a 8X3 table to a vertical range of the Unique stringsonly
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 |
#6
|
|||
|
|||
Convert a 8X3 table to a vertical range of the Unique strings
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 |
#7
|
|||
|
|||
Convert a 8X3 table to a vertical range of the Unique strings
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. |
#8
|
|||
|
|||
Convert a 8X3 table to a vertical range of the Unique strings
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 |
#9
|
|||
|
|||
Convert a 8X3 table to a vertical range of the Unique strings
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 |
Thread Tools | |
Display Modes | |
|
|