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.
 
Sort by date Sort by votes
Oh sorry my ninja edit wasn't ninja enough. I forgot to select the headers and it came out weird. So how do I change the VBA based on the rows and columns that I have (which will vary with files?)
 
Upvote 0

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Unless you show me your configuration, I cannot give you an answer as my crystal ball is broken.
 
Upvote 0
Thanks for your reply. I can't answer that specifically because each file is different ,some have 200 rows and 100 columns, some have 1000 rows and 5 columns for example. I just wanted to inquire how your VBA worked so I can tweak it as needed. I understand that you will not be able to manually adjust it for me. I will have 3 columns as an end result always.
 
Upvote 0
My bad, I misunderstood your request

The rows are already taken care of by the use of the lrS and lrT variables. You will need to add a line of code to Dim a new variable for the last column

Code: 
 Dim lc as long
[TABLE="width: 19"]
<tbody>[TR]
[TD] lc=Cells(1,Columns.Count).End(xlToLeft).Column[/TD]
[/TR]
</tbody>[/TABLE]

Then where I have used the letter D to signify the right most column, you would use the variable lc.

Now you have a variable for both the last rows and the last column.

If you encounter more issues, then post back for clarification.
 
Upvote 0
Her's the new full code. I thought I better give it to you as there is a slight change in how to format the code when the last column is defined as an integer or long integer

Code: 
Option Explicit


Sub UnPivot()
'Set your variables
    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
    Dim lc As Long [COLOR=#ff0000]'new line[/COLOR]
    lc = Cells(1, Columns.Count).End(xlToLeft).Column [COLOR=#b22222]'new line[/COLOR]


Application.ScreenUpdating = False 'sets screen to update after all is completed so screen does not flutter
    With w1  'work within sheet1
        For i = 2 To lrS  'sets variable to select rows 2 to last row and loop
        lrT = w2.Range("B" & Rows.Count).End(xlUp).Row  'sets the last row in the target worksheet
            .Range("A" & i).Copy w2.Range("A" & lrT + 1) 'copies the range A and row i (variable) to new sheet and places in row after last row
            .Range(Cells(i, 2), Cells(i, lc)).Copy [COLOR=#b22222]'changed  [/COLOR]copies range B to last column in variable row[COLOR=#b22222][/COLOR]
            w2.Range("B" & lrT + 1).PasteSpecial xlPasteAll, , , True 'pastes to column B in target sheet
            .Range(Cells(1, 2), Cells(1, lc)).Copy [COLOR=#b22222]'changed [/COLOR]copies range  B1 to last column in row 1[COLOR=#b22222][/COLOR]
            w2.Range("C" & lrT + 1).PasteSpecial xlPasteAll, , , True 'pastes to column C in target sheet
        Next i
End With
Application.CutCopyMode = False




    With w2  'using the target sheet
    lrT = .Range("B" & Rows.Count).End(xlUp).Row  'finds last row used in column B
        For i = 3 To lrT 'Sets loop
        If .Range("A" & i) = "" Then  'if range A and variable row is empty 
            .Range("A" & i) = .Range("A" & i - 1) 'then copy the value in cell above and paste to it
        End If
        Next i
    End With
Application.ScreenUpdating = True
MsgBox "complete"


End Sub
 
Last edited:
Upvote 0
darkangst said:
??? I have no idea what this is doing, I added the entire page and selected 0 page fields is that right?
Click to expand...
Don't add the page, but selecting 0 page fields is correct. Here is more detail:
Use ALT+D+P, select MULTIPLE CONSOLIDATION RANGES then PivotTable, click next. Select I WILL CREATE THE PAGE FIELDS, click next. Insert by highlighting the range of the matrix, Don't Add, select 0 page filds, click next, click new worksheet, click Finish. All those steps just gave you a pivot table, but if you double click the bottom right GRAND Total, that will generate the new table you want on another sheet.

Edit: This is where I learned it from some time ago. A short video by Mr. Excel. http://www.youtube.com/watch?v=kfWzkp92eEc
</SPAN>
 
Last edited:
Upvote 0
alansidman said:
Her's the new full code. I thought I better give it to you as there is a slight change in how to format the code when the last column is defined as an integer or long integer

Code: 
Option Explicit


Sub UnPivot()
'Set your variables
    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
    Dim lc As Long [COLOR=#ff0000]'new line[/COLOR]
    lc = Cells(1, Columns.Count).End(xlToLeft).Column [COLOR=#b22222]'new line[/COLOR]


Application.ScreenUpdating = False 'sets screen to update after all is completed so screen does not flutter
    With w1  'work within sheet1
        For i = 2 To lrS  'sets variable to select rows 2 to last row and loop
        lrT = w2.Range("B" & Rows.Count).End(xlUp).Row  'sets the last row in the target worksheet
            .Range("A" & i).Copy w2.Range("A" & lrT + 1) 'copies the range A and row i (variable) to new sheet and places in row after last row
            .Range(Cells(i, 2), Cells(i, lc)).Copy [COLOR=#b22222]'changed  [/COLOR]copies range B to last column in variable row
            w2.Range("B" & lrT + 1).PasteSpecial xlPasteAll, , , True 'pastes to column B in target sheet
            .Range(Cells(1, 2), Cells(1, lc)).Copy [COLOR=#b22222]'changed [/COLOR]copies range  B1 to last column in row 1
            w2.Range("C" & lrT + 1).PasteSpecial xlPasteAll, , , True 'pastes to column C in target sheet
        Next i
End With
Application.CutCopyMode = False




    With w2  'using the target sheet
    lrT = .Range("B" & Rows.Count).End(xlUp).Row  'finds last row used in column B
        For i = 3 To lrT 'Sets loop
        If .Range("A" & i) = "" Then  'if range A and variable row is empty 
            .Range("A" & i) = .Range("A" & i - 1) 'then copy the value in cell above and paste to it
        End If
        Next i
    End With
Application.ScreenUpdating = True
MsgBox "complete"


End Sub
Click to expand...




I have a question about this code that i was wondering if you could answer.

if my data table starts in cell "C9" instead of "A1", how would this macro have to be adjusted to suit?
 
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,911
Messages
6,175,334
Members
452,636
Latest member
laura12345

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