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.

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.

shabaz! *Imperial Silver Medaled* Thanks Peo!

"Peo Sjoblom" wrote:

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.