unique alpha's in order
 

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<"")*(C OUNTIF(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?


--
ceemo
------------------------------------------------------------------------
ceemo's Profile: http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=396150

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<"")*(COUNTI F(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

"ceemo" wrote in
message ...

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<"")*(C OUNTIF(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?


--
ceemo
------------------------------------------------------------------------
ceemo's Profile:
http://www.excelforum.com/member.php...o&userid=10650
View this thread: http://www.excelforum.com/showthread...hreadid=396150

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(SMAL L(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!

ceemo Wrote:
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<"")*(C OUNTIF(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?


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=396150