transpose multiple rows and columns

[prawer]

Hi There,

I appreciate how transpose can be used to rotate individual columns or rows. My situation is, I believe, somewhat different.

I have data that looks like this:

A 1 2 3 4 5 5 5 5
B 2 4 6 7 8 5 5 5
C 3 4 9 7 5 5 5 5

I want it to look like this:

A 1
A 2
A 3
A 4
A 5
A 5
A 5
A 5
B 2
B 4
B 6
B 7
B 8
B 5
B 5
B 5
C 3
C 4
C 9
C 7
C 5
C 5
C 5
C 5

Any help you can provide on this would be greatly appreciated. Thanks!

[zbor]

try this:

in first column (for ABC): =INDEX($A$1:$I$3;INT((ROW(A1)-1)/COUNT($B$1:$I$1))+1;1)
in 2nd row (for numbers): =INDEX($B$1:$I$3;INT((ROW(B1)-1)/COUNT($B$1:$I$1))+1;MOD((ROW(B1)-1);COUNT($B$1:$I$1))+1)

and extend down...

(replace ; for , if needed...
It will work for various number of rows and columns as long as they are equal size (like here, 3 rows x 8 columns)

[prawer]

works. thanks!