Is it possible to “unpivot” or “reverse pivot” in Excel?

Question

I have data that looks like this:

Id | Loc1 | Loc2 | Loc3 | Loc4
------------------------------
1  | NY   | CA   | TX   | IL
2  | WA   | OR   | NH   | RI

And I want to convert it to this:

Id | LocNum | Loc
-----------------
1  |   1    | NY
1  |   2    | CA
1  |   3    | TX
1  |   4    | IL
2  |   1    | WA
2  |   2    | OR
2  |   3    | NH
2  |   4    | RI

What's the easiest way to do this in Excel 2007?

Answer 1

You can do this with a pivot table.

1. Create a "Multiple Consolidation Ranges PivotTable." (Only on Pivot Table Wizard. Callup with ALT+D, P on Excel 2007)
2. Select "I will create my own page fields".
3. Select your data.
4. Double click on the grand total value - the one at the intersection of Row Grand and Column Grand, all the way on the lower right hand corner of your pivot table.

You should see a new sheet containing all of the data in your pivot table, transposed in the way you're looking for.

Datapig technologies provides step-by-step instructions that are actually more complicated than you need - his example transposes only part of the data set & uses the pivot technique combined with TextToColumns. But it does have lots of pictures.

Note that a pivot table will group the data. If you want it ungrouped, the only way to do it is to copy the pivot table, and "paste special" as values. You can then fill in the blanks with a technique like this: http://www.contextures.com/xlDataEntry02.html

Answer 2

Best I've come up with so far is this:

Id   LocNum  Loc
---------------------------------
1    1       =INDEX(Data,A6,B6)
1    2       =INDEX(Data,A7,B7)
1    3       =INDEX(Data,A8,B8)
1    4       =INDEX(Data,A9,B9)
2    1       =INDEX(Data,A10,B10)
2    2       =INDEX(Data,A11,B11)
2    3       =INDEX(Data,A12,B12)
2    4       =INDEX(Data,A13,B13)

This works, but I have to generate the Id's and LocNum's manually. If there's a more automated solution (besides writing a macro), please let me know in a separate answer.

Answer 3

You seem to have gotten the "loc" column (evidenced by your first answer), and now you need help getting the other two columns.

Your first option is to simply type the first several (say, 12) rows into those columns and drag down - I think Excel does the right thing in this case (I don't have excel on this computer to test it for sure).

If that doesn't work, or if you want something more programmer-y, try using the row() function. Something like "=Floor(row()/4)" for the ID column and "=mod(row(),4)+1" for the LocNum column.

Answer 4

There is a parametric VBA conversion utility to unpivot or reverse pivoted data back to a database table, please see

http://www.spreadsheet1.com/unpivot-data.html