View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove[_2_] Harlan Grove[_2_] is offline
external usenet poster
 
Posts: 1,231
Default 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.