Is there a formula or automated way to restructure and stack columns as shown in the image

I am looking for an automated way to go from the example table on the left to the example table on the right. The only thing I can currently think of is to do it manually but this would take hours based on the size of the worksheet. If this is possible please steer me in the right direction.

Current Table Left | Desired Table Right

Example.png

Assuming your data occupies cells A2:C11, with row 1 for the headers, you can use these formulae in the cells stated:

E2: =INT((ROWS($1:1)-1)/2)+1

F2: =INDEX($B$1:$C$1,MOD(ROWS($1:1)-1,2)+1)

G2: =INDEX($B$2:$C$11,INT((ROWS($1:1)-1)/2)+1,MOD(ROWS($1:1)-1,2)+1)

Copy the 3 formulae down as far as you need to.

Hope this helps.

Pete

Another option,

Please Login or Register  to view this content.

Thank you! This worked perfectly for what I need!

You are welcome.
--------------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.

Try this,

Please Login or Register  to view this content.