transposing question...

[Guest]

There's an article here:
http://office.microsoft.com/en-gb/assistance/HA012260381033.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.

 

[Peo Sjoblom]

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),INDEX($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)),COLUMN(A:A)),2))


now I am eagerly waiting the imperial silver medal <bg>

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[Guest]

shabaz! *Imperial Silver Medaled* Thanks Peo!

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),INDEX($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)),COLUMN(A:A)),2))


now I am eagerly waiting the imperial silver medal <bg>

--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com