Unpivot a table?

D

darkangst

New Member
Joined
Mar 18, 2013
Messages
19
Hi,

So my table for example looks like this:

[TABLE="width: 256"]
<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]Empl_ID[/TD]
[TD="width: 64"]A2[/TD]
[TD="width: 64"]A3[/TD]
[TD="width: 64"]A4[/TD]
[/TR]
[TR]
[TD="align: right"]11111[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]14044[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]14070[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]14167[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]14199[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD="align: right"]14210[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]9[/TD]
[/TR]
</tbody>[/TABLE]

I would like it to look like:

[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1111[/TD]
[TD="width: 64, align: right"]1[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]A2[/TD]
[/TR]
[TR]
[TD="align: right"]1111[/TD]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD="align: right"]1111[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]A4[/TD]
[/TR]
[TR]
[TD="align: right"]14044[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD="align: right"]14044[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD="align: right"]14044[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]A4[/TD]
[/TR]
[TR]
[TD="align: right"]14070[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD="align: right"]14070[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD="align: right"]14070[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]A4[/TD]
[/TR]
[TR]
[TD="align: right"]14167[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD="align: right"]14167[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD="align: right"]14167[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]A4[/TD]
[/TR]
[TR]
[TD="align: right"]14199[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD="align: right"]14199[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD="align: right"]14199[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]A4[/TD]
[/TR]
[TR]
[TD="align: right"]14210[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD]A2[/TD]
[/TR]
[TR]
[TD="align: right"]14210[/TD]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD]A3[/TD]
[/TR]
[TR]
[TD="align: right"]14210[/TD]
[TD="align: right"]9[/TD]
[TD][/TD]
[TD]A4[/TD]
[/TR]
</tbody>[/TABLE]

I was trying to use offset, indirect.. and got confused. I feel like this is simple but I just don't know how to do it. I still can't do this in reverse either haha.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Do you only need to do this once ?

If yes, you could do this in a couple of seconds with Copy and Paste, plus a Data Sort at the end.
 
Upvote 0
Here is one technique to do that. I use it often.
Use ALT+D+P, select MULTIPLE CONSOLIDATION RANGES, click next. Select I WILL CREATE THE PAGE FIELDS, click next. Insert the range of the matrix, click next, click Finish. </SPAN>

You will get a Pivot Table: Double click the Grand Total and that will finish the consolidation and presents itself on another worksheet.</SPAN>
 
Upvote 0
I could only think of a VBA Solution for you

Code: 
Option Explicit


Sub UnPivot()
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim i As Long
    Dim lrS As Long
    Dim lrT As Long
    Set w1 = Sheets("Sheet1")
    Set w2 = Sheets("Sheet2")
    lrS = w1.Range("A" & Rows.Count).End(xlUp).Row


Application.ScreenUpdating = False
    With w1
        For i = 2 To lrS
        lrT = w2.Range("B" & Rows.Count).End(xlUp).Row
            .Range("A" & i).Copy w2.Range("A" & lrT + 1)
            .Range("B" & i & ":D" & i).Copy
            w2.Range("B" & lrT + 1).PasteSpecial xlPasteAll, , , True
            .Range("B1:D1").Copy
            w2.Range("C" & lrT + 1).PasteSpecial xlPasteAll, , , True
        Next i
End With
Application.CutCopyMode = False




    With w2
    lrT = .Range("B" & Rows.Count).End(xlUp).Row
        For i = 3 To lrT
        If .Range("A" & i) = "" Then
            .Range("A" & i) = .Range("A" & i - 1)
        End If
        Next i
    End With
Application.ScreenUpdating = True
MsgBox "complete"


End Sub

How to install your new code

  1. Copy the Excel VBA code
  2. Select the workbook in which you want to store the Excel VBA code
  3. Press Alt+F11 to open the Visual Basic Editor
  4. Choose Insert > Module
  5. Edit > Paste the macro into the module that appeared
  6. Close the VBEditor
  7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
  1. Press Alt-F8 to open the macro list
  2. Select a macro in the list
  3. Click the Run button
 
Upvote 0
Gerald Higgins said:
Do you only need to do this once ?

If yes, you could do this in a couple of seconds with Copy and Paste, plus a Data Sort at the end.
Click to expand...

I only put out that example, the actual file is about 100 columns across and 1000 rows down. And I will need to do it more than once.
 
Upvote 0
Trouttrap2 said:
Here is one technique to do that. I use it often.
Use ALT+D+P, select MULTIPLE CONSOLIDATION RANGES, click next. Select I WILL CREATE THE PAGE FIELDS, click next. Insert the range of the matrix, click next, click Finish.

You will get a Pivot Table: Double click the Grand Total and that will finish the consolidation and presents itself on another worksheet.
Click to expand...

??? I have no idea what this is doing, I added the entire page and selected 0 page fields is that right?
 
Upvote 0
Trouttrap2 said:
Here is one technique to do that. I use it often.
Use ALT+D+P, select MULTIPLE CONSOLIDATION RANGES, click next. Select I WILL CREATE THE PAGE FIELDS, click next. Insert the range of the matrix, click next, click Finish.

You will get a Pivot Table: Double click the Grand Total and that will finish the consolidation and presents itself on another worksheet.
Click to expand...

Oh wait, I see. When you double click the cell, it'll create what I want on 6 different worksheets. =/ Is there a way to make it do it on one worksheet in one column?
 
Upvote 0
alansidman said:
I could only think of a VBA Solution for you

Code: 
Option Explicit


Sub UnPivot()
    Dim w1 As Worksheet
    Dim w2 As Worksheet
    Dim i As Long
    Dim lrS As Long
    Dim lrT As Long
    Set w1 = Sheets("Sheet1")
    Set w2 = Sheets("Sheet2")
    lrS = w1.Range("A" & Rows.Count).End(xlUp).Row


Application.ScreenUpdating = False
    With w1
        For i = 2 To lrS
        lrT = w2.Range("B" & Rows.Count).End(xlUp).Row
            .Range("A" & i).Copy w2.Range("A" & lrT + 1)
            .Range("B" & i & ":D" & i).Copy
            w2.Range("B" & lrT + 1).PasteSpecial xlPasteAll, , , True
            .Range("B1:D1").Copy
            w2.Range("C" & lrT + 1).PasteSpecial xlPasteAll, , , True
        Next i
End With
Application.CutCopyMode = False




    With w2
    lrT = .Range("B" & Rows.Count).End(xlUp).Row
        For i = 3 To lrT
        If .Range("A" & i) = "" Then
            .Range("A" & i) = .Range("A" & i - 1)
        End If
        Next i
    End With
Application.ScreenUpdating = True
MsgBox "complete"


End Sub

How to install your new code

  1. Copy the Excel VBA code
  2. Select the workbook in which you want to store the Excel VBA code
  3. Press Alt+F11 to open the Visual Basic Editor
  4. Choose Insert > Module
  5. Edit > Paste the macro into the module that appeared
  6. Close the VBEditor
  7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:
  1. Press Alt-F8 to open the macro list
  2. Select a macro in the list
  3. Click the Run button
Click to expand...

Oh this works! Thank you so much! Can you explain what it is doing? (will i need to alter anything if I have more columns/rows for example?)
 
Last edited:
Upvote 0
Thank you all for helping me so far. At least I don't feel as bad that it's not as easy as I thought it was.
 
Upvote 0
Here is how the file looked before I applied the macro.

Excel Workbook
ABCD
1Empl_IDA2A3A4
211111189
314044299
414070329
514167429
614199549
714210679
Sheet1


If your set up is different then you will get different results
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
VB to rename tabs based on cell information in first tab
Replies
4
Views
166
DanteAmor
DanteAmor
SaraWitch
  • Solved
Add zero to beginning of number extraction
2
Replies
10
Views
699
MARK858
MARK858
C
  • Question Question
ComboBox and TextBox
Replies
0
Views
340
Cr4ZyPP
C
M
  • Solved
JOIN Arrays in Formula
Replies
7
Views
452
Rick Rothstein
Rick Rothstein
F
  • Solved
Sort data without changing the set of each data (using VBA)
2
Replies
12
Views
404
feni1388
F

Forum statistics

Threads
1,223,909
Messages
6,175,314
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top