How to transform an Excel table to another format

Question

I have the following Excel Table:

I would like to transform it into another Table with only 3 columns: the first one with A,B,...C,D,A,B,...,D,..., the second one with 1,2,...,10, and the third one with the corresponding value in the above table.

For example:

• The first line would have the cells A - 1 - 30
• The second line would have the cells A - 2 - 78

• ...

• The last line would have the cells H - 10 - 102

Obviously this can be done manually, but I need a faster way to achieve it. Any help is appreciated! Thanks

Answer 1

In Cell K1 enter the following formula

=INDEX($B$1:$I$1,1,1+INT((ROW(A1)-1)/ROWS($A$2:$A$11)))

then in Cell L1 enter

=INDEX($A$2:$A$11,MOD(ROW(A1)-1+ROWS($A$2:$A$11),ROWS($A$2:$A$11))+1,1)

finally, in Cell M1 enter

=INDEX($B$2:$I$11,MOD(ROW(A1)-1+ROWS($A$2:$A$11),ROWS($A$2:$A$11))+1,1+INT((ROW(A1)-1)/ROWS($A$2:$A$11)))

Drag/Copy down above formulas as required. See image for reference.