How do I flatten columns into rows?

[edtguy]

Newbie question (I'm not sure whether or not this is a pivot problem): How do I flatten columns B through ...n, such that the heading in row 1 of each of those columns become the column B value and the values in row 2 through ...n of each column are moved to column C, with each successive column's values moved underneath the previous column's values?

Currently, column A row values are a list of categories and row 1 headers are location names, with each cell beginning with B2 being a percentage value.

I've created a simple example below, where a table with 4x2 values becomes a table with 3x6 values, but assume a real table would have 44 categories and 12 locations, so a table with 13x44 values becomes a table with 3x528 values.

Is this do-able with formulas? Any assistance is appreciated.

Current State:

Category	Warehouse 1	Warehouse 2	Warehouse 3
Widgets	30%	60%	18%
Stuff	70%	40%	82%

Desired State:

Category	Location	Percentage
Widgets	Warehouse 1	30%
Stuff	Warehouse 1	70%
Widgets	Warehouse 2	60%
Stuff	Warehouse 2	40%
Widgets	Warehouse 3	18%
Stuff	Warehouse 3	82%

[Pete_UK]

Welcome to the forum.

It would help if you attached a sample Excel workbook, then we could give you a solution which is geared exactly to your file.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Do NOT try to use the Paperclip icon, as this does not work on this forum.

Hope this helps.

Pete

[Special-K]

in Sheet2!A2
=INDEX(Sheet1!A$2:A$3,NOT(MOD((ROW()-1),2))+1,1)

in Sheet2!B2
=INDEX(B$1:D$1,1,MOD(ROW()-2,3)+1)

in Sheet2!C2
=INDEX(B$2:D$3,QUOTIENT(ROW()-2,3)+1,MOD(ROW()-2,3)+1)

copy down the columns

Change the numbers in red to adjust row and column size of the table

[edtguy]

Thanks Pete. I'll remember that for next time, as Special-K has resolved my issue for me on this one.

[edtguy]

in Sheet2!A2
=INDEX(Sheet1!A$2:A$3,NOT(MOD((ROW()-1),2))+1,1)

in Sheet2!B2
=INDEX(B$1:D$1,1,MOD(ROW()-2,3)+1)

in Sheet2!C2
=INDEX(B$2:D$3,QUOTIENT(ROW()-2,3)+1,MOD(ROW()-2,3)+1)

copy down the columns

Change the numbers in red to adjust row and column size of the table

Thanks! With a little fiddling, I was able to get to the desired state. Here are the modified formulas:

in Sheet2!A2
=INDEX({source sheet}!$A$2:$A${last row},IF(MOD((ROW()-1),{data rows})=0,{data rows},MOD((ROW()-1),{data rows})),1)

in Sheet2!B2
=INDEX({source sheet}!$B$1:${last column}!$1,1,MOD(ROW()-2,{data columns})+1)

in Sheet2!C2
=INDEX({source sheet}!$B$2:${last column}!${last row}!,MOD(ROW()-2,{data rows})+1,QUOTIENT(ROW()-2,{data rows})+1)

No doubt these formulas can be refined further, and use replaceable parameters, but it works for me meeting a deadline. Thanks again!

[edtguy]

Thanks! With a little fiddling, I was able to get to the desired state. Here are the modified formulas:

in Sheet2!A2
=INDEX({source sheet}!$A$2:$A${last row},IF(MOD((ROW()-1),{data rows})=0,{data rows},MOD((ROW()-1),{data rows})),1)

in Sheet2!B2
=INDEX({source sheet}!$B$1:${last column}!$1,1,MOD(ROW()-2,{data columns})+1)

in Sheet2!C2
=INDEX({source sheet}!$B$2:${last column}!${last row}!,MOD(ROW()-2,{data rows})+1,QUOTIENT(ROW()-2,{data rows})+1)

No doubt these formulas can be refined further, and use replaceable parameters, but it works for me meeting a deadline. Thanks again!

Whoops! I didn't review the result carefully. The location value needs to repeat over the set of categories before moving on to the next location value. The formula for column B was incorrect. Here it is corrected:

in Sheet2!B2
=INDEX({source sheet}!$B$1:${last column}!$1,1,TRUNC((ROW()-2)/{data columns})+1)