Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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?

share|improve this question
add comment

8 Answers

up vote 29 down vote accepted

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

share|improve this answer
    
Thanks for your answer, but I'm not quite sure what you're saying here. For the example in my question, what would I use for Row Labels, Column Labels, Values, and Report Filter? –  devuxer Dec 3 '09 at 21:05
    
If I put Id into Row Labels and Loc1 through Loc4 into Column Labels, then click the lower-right cell (the intersection of the Grand Totals), it does create a new sheet with a table, but it's not what I want. It's basically just a copy of the regular pivot table. –  devuxer Dec 3 '09 at 21:20
    
I'm sorry - I left out a critical piece of data. The pivot Table must be a 'multiple consolidation range' type pivot table, even though in this case you have nothing to consolidate. –  DaveParillo Dec 3 '09 at 23:19
    
Dave, thanks for editing your answer, but are you sure these instructions apply to Excel 2007? I'm not seeing anything about "multiple consolidation ranges". –  devuxer Dec 7 '09 at 20:06
3  
Ah okay, I looked it up in the help and found a back-door way to get the old Pivot Table Wizard in Excel 2007 (you need to press ALT+D+P). Now I'm able to follow your steps, and it seems to work. Thanks for your help! –  devuxer Dec 7 '09 at 20:44
show 4 more comments

If your data isn't an Excel Pivot Table but just data, you might want to "un-pivot" it with some simple VBA code. The code depends on two named ranges, Source and Target. Source is the data you want to un-pivot (exclusive of the column/row headers, e.g. NY-RI in the sample) and Target is the first cell where you want to place your result.

Sub unPivot()
Dim oTarget As Range
Dim oSource As Range
Dim oCell As Range

Set oSource = Names("Source").RefersToRange
Set oTarget = Names("Target").RefersToRange

For Each oCell In oSource
    If oCell.Value <> "" Then
        oTarget.Activate
      ' get the column header
        oTarget.Value = oCell.Offset(-(oCell.Row - oSource.Row + 1), 0).Text 
      ' get the row header
         oTarget.Offset(0, 1).Value = oCell.Offset(0, _
           -(oCell.Column - oSource.Column + 1)).Text 
      ' get the value
        oTarget.Offset(0, 2).Value = oCell.Text 
      ' move the target pointer to the next row
        Set oTarget = oTarget.Offset(1, 0) 
    End If
Next
Beep
End Sub
share|improve this answer
    
Thanks for this. Works as excepted and make me save a lot of time. –  tigrou Apr 22 '13 at 14:56
add comment

I have built an add-in that will let you do that, and that makes it easy to adapt to different situations. Check it out here: http://tduhameau.wordpress.com/2012/09/24/the-unpivot-add-in/

share|improve this answer
    
Life saver - thanks.! –  Marc Nov 22 '13 at 18:41
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
1  
Welcome to Super User! Whilst this may theoretically answer the question, it would be preferable to include the essential parts of the answer here, and provide the link for reference. –  Peachy Nov 6 '12 at 16:55
add comment

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.

share|improve this answer
add comment

It's much simple than this. Just click the "Transpose" option in "Paste Special..." to get the transposition you are requesting.

share|improve this answer
1  
This wouldn't work. –  zx8754 Oct 7 '13 at 10:34
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.