bert
[Entry]
"If the dimensions of your data are as in the sample provided in your question, then the following set of formulae using OFFSET should give you your required result: Assuming
1 | NY | CA | TX | IL 2 | WA | OR | NH | RI
are in the range A2:E3, then enter
=OFFSET($A$2,FLOOR((ROW(A2)-ROW($A$2))/4,1),0)
in F2, say, and
=MOD(ROW(A2)-ROW($A$2),4)+1
in G2, say, and
=OFFSET($B$2,FLOOR((ROW(B2)-ROW($B$2))/4,1),MOD(ROW(A2)-ROW($A$2),4))
in H2, say. Then copy these formulae down as far as required. This is the easiest, pure, built-in formula solution that I can think of." "If the dimensions of your data are as in the sample provided in your question, then the following set of formulae using OFFSET should give you your required result: Assuming
1 | NY | CA | TX | IL 2 | WA | OR | NH | RI
are in the range A2:E3, then enter
=OFFSET($A$2,FLOOR((ROW(A2)-ROW($A$2))/4,1),0)
in F2, say, and
=MOD(ROW(A2)-ROW($A$2),4)+1
in G2, say, and
=OFFSET($B$2,FLOOR((ROW(B2)-ROW($B$2))/4,1),MOD(ROW(A2)-ROW($A$2),4))
in H2, say. Then copy these formulae down as far as required. This is the easiest, pure, built-in formula solution that I can think of."
|