Unpivot a table?

[darkangst]

Hi,

So my table for example looks like this:

Empl_ID	A2	A3	A4
11111	1	8	9
14044	2	9	9
14070	3	2	9
14167	4	2	9
14199	5	4	9
14210	6	7	9

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

I would like it to look like:

1111	1		A2
1111	8		A3
1111	9		A4
14044	2		A2
14044	9		A3
14044	9		A4
14070	3		A2
14070	2		A3
14070	9		A4
14167	4		A2
14167	2		A3
14167	9		A4
14199	5		A2
14199	4		A3
14199	9		A4
14210	6		A2
14210	7		A3
14210	9		A4

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

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.

 

[Gerald Higgins]

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.

 

[Trouttrap2]

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>

 

[alansidman]

I could only think of a VBA Solution for you

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

 

[darkangst]

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.

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.

 

[darkangst]

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.

??? I have no idea what this is doing, I added the entire page and selected 0 page fields is that right?

 

[darkangst]

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.

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?

 

[darkangst]

I could only think of a VBA Solution for you

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

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?)

 

[darkangst]

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.

 

[alansidman]

Here is how the file looked before I applied the macro.

Excel Workbook
	A	B	C	D
1	Empl_ID	A2	A3	A4
2	11111	1	8	9
3	14044	2	9	9
4	14070	3	2	9
5	14167	4	2	9
6	14199	5	4	9
7	14210	6	7	9
Sheet1

If your set up is different then you will get different results