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

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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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(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!

Originally Posted by 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?

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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> 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<>"")*(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

"ceemo" <ceemo.1tv72x_1124208631.9465@excelforum-nospam.com> wrote in
message news:ceemo.1tv72x_1124208631.9465@excelforum-nospam.com...
>
> 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?
>
>
> --
> ceemo
> ------------------------------------------------------------------------
> ceemo's Profile:
> http://www.excelforum.com/member.php...o&userid=10650
> View this thread: http://www.excelforum.com/showthread...hreadid=396150
>