First of all there is a more waterproof way of doing this, using the same
ranges
=IF(ROWS(B$1:B1)<=COUNTIF($A$1:$A$7,$A$10),INDEX($ B$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)-ROW($A$1)+1),ROWS(B$1:B1))),"")
also entered with ctrl + shift & enter
compare the 2 formulas, then insert an empty row at the top and you'll see
what I mean, the formula you posted will return wrong answer
Example can be downloaded here
http://nwexcelsolutions.com/Download/
to extract the values across you can use
=IF(COLUMNS(B$1:B1)<=COUNTIF($A$1:$A$7,$A$10),INDE X($B$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7 )-COLUMN($A$1)+1),COLUMNS($B:B))),"")
or if you prefer the first formula
=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),COLUMN(A:A)),2)),"",INDEX($A$1: $B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),COLU MN(A:A)),2))
now I am eagerly waiting the imperial silver medal <bg
--
Regards,
Peo Sjoblom
http://nwexcelsolutions.com
"cherrynich" wrote in message
...
There's an article he
http://office.microsoft.com/en-gb/as...260381033.aspx
It explains exactly what I'm trying to do almost, I want to transpose this
array (from the article close to the bottom):
=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$ 10,ROW($A$1:$A$7)),
ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$ 7=$A$10,ROW($A$1:$A$7)),
ROW(1:1)),2))
I want it to list the numbers not vertically but horizontally. Is there
any
way I could make the array display it that way by entering something into
this formula?
I will award an imperial silver medal to anyone who can answer this.