unique item array

[boris]

I once saw a multi-cell array in a book that looked at a
defined range, and by virtue of the way this array
definition was structured, it was able to produce a sorted
list of unique items from the range within this multicell
array (sorted in ascending order, for example), leaving
any unused cells blank (if there were less unique items
than the span of this array).

I know it might be a tall order, but if anyone can come up
with this, you will be a life saver.

I know that this can be done with macros, but I
specifically would like to use a multi-cell array
solution. Thanks. Boris

 

[Guest]

Hi Boris
should this apply for a mixture of data types (numeric values, text, etc.)
If yes try the following formulas (all array entered with CTRL+SHIFT+eNTER):
cell B1:
=INDEX($A$1:$A$21,MATCH(MIN(COUNTIF($A$1:$A$21,"<"&$A$1:$A$21&"")+
COUNT($A$1:$A$21)*ISTEXT($A$1:$A$21)+BigNumber*ISBLANK($A$1:$A$21)),COUNTIF($A$1:$A$21,"<"&$A$1:$A$21&"")+COUNT($A$1:$A$21)*ISTEXT($A$1:$A$21)+BigNumber*ISBLANK($A$1:$A$21),0))

cell B2:
=INDEX($A$1:$A$21,MATCH(0,COUNTIF($A$1:$A$21,"<"&$A$1:$A$21)+COUNT
($A$1:$A$21)*ISTEXT($A$1:$A$21)+BigNumber*ISBLANK($A$1:$A$21)-SUM(COUNTIF($A$1:$A$21,"="&B$1:B1)),0))

Where
BigNumber is a defined name with an assigned huge number (e.g. 70000). You
could replace BigNumber in the formula directly with such a number if you
want.

Notes:
- may produce an error if your range (in this case A1:A21) conatins a zero
length string (="") but the formula should deal with all other types of data
(including error values)
- Uses the same sort direction as Excel does
- Currently no error checking included if the list of unique values is
exhausted. Two ways to achieve this:
a) use a second column with the formula
=IF(ISERROR(B1),"",B1)
b) use this ISERROR approach directly in the formula and replace B1 with the
above formulas.

Now just curious: In which book did you see such type of formulas?

 

[Biff]

Hi Frank!

You couldn't get that in a single formula? <g>

Biff

 

[Guest]

Hi Biff
I leave this for others <vbg>
Though I'm not sure it is feasible at all. Maybe Harlan has a better /
single-formula approach

 

[Biff]

Now just curious: In which book did you see such type of
formulas?

Excel for Dummies

Biff

 

[Guest]

lol
I just checked John Walkenbach's Formulas book and though quite advanced I
haven't found such very specific formulas in it (and IMHO they also should
not be in such a book).