unique alpha's in order

[ceemo]

Hi i have the following which gives me unique alpha's in order but when
it has produced all possible entry's it diplays an error.

=INDEX($A$1:$A$6,MATCH(SMALL(IF(($A$1:$A$6<>"")*(COUNTIF(OFFSET($A$1,0,0,ROW($A$1:$A$6)-ROW($A$1)+1),$A$1:$A$6)=1),COUNTIF($A$1:$A$6,"<"&$A$1:$A$6)),ROWS($B$1:B1)),COUNTIF($A$1:$A$6,"<"&$A$1:$A$6),0))


I have tried using the below but it didnt work and it made the cell
very large
=if(iserror(formula)=true,"",formula


Can anyone help?

 

[Biff]

Hi!

With long formulas like that it's easier to use conditional formatting to
hide the errors.

Select the cell containing the formula
Conditional Formatting
Formula is: =ISERROR(cell_reference)
As the format, set the font color to be the same as the background color.

If you want the error trap (makes the formula twice as long and twice as
long to process):

=IF(ISERROR(MATCH(SMALL(IF(($A$1:$A$6<>"")*(COUNTIF(OFFSET($A$1,0,0,ROW($A$1:$A$6)-ROW($A$1)+1),$A$1:$A$6)=1),COUNTIF($A$1:$A$6,"<"&$A$1:$A$6)),ROWS($B$1:B1)),COUNTIF($A$1:$A$6,"<"&$A$1:$A$6),0)),"",INDEX($A$1:$A$6,MATCH(SMALL(IF(($A$1:$A$6<>"")*(COUNTIF(OFFSET($A$1,0,0,ROW($A$1:$A$6)-ROW($A$1)+1),$A$1:$A$6)=1),COUNTIF($A$1:$A$6,"<"&$A$1:$A$6)),ROWS($B$1:B1)),COUNTIF($A$1:$A$6,"<"&$A$1:$A$6),0)))

Biff

 

[Domenic]

Here's another way...

C1:

=SUMPRODUCT((A1:A6<>"")/COUNTIF(A1:A6,A1:A6&""))

B1, copied down:

=IF(ROWS($B$1:B1)<=$C$1,INDEX($A$1:$A$6,MATCH(SMALL(IF(($A$1:$A$6<>"")*(COUNTIF(OFFSET($A$1,0,0,ROW($A$1:$A$6)-ROW($A$1)+1),$A$1:$A$6)=1),COUNTIF($A$1:$A$6,"<"&$A$1:$A$6)),ROWS($B$1:B1)),COUNTIF($A$1:$A$6,"<"&$A$1:$A$6),0)),"")

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!