Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count unique with conditions | Excel Worksheet Functions | |||
How to change Series Order in a Combination Chart? | Excel Discussion (Misc queries) | |||
Maintaining a default worksheet order | Excel Discussion (Misc queries) | |||
Data association | Excel Worksheet Functions | |||
Count unique alpha numeric "characters" in a common cell | Excel Worksheet Functions |