Dynamically unpivot data from a table to a new one

M

Mechixx

Board Regular
Joined
Oct 15, 2015
Messages
59
Hello there everyone!

I'm in a bit of a kerfuffle... and not really sure how to go about this. I've created a workbook that multiple people will be entering data into and its going to broadcast how many of each insert we will need per month.

So right now i have a table that looks like this that has many more rows, and there will always be more and more rows being added.
The table is called "ToolingData_Table"

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Dept.[/TD]
[TD]Part #[/TD]
[TD]Op #[/TD]
[TD]Type of Item[/TD]
[TD]Item Code[/TD]
[TD]Stock #[/TD]
[TD]# of Corners[/TD]
[TD]# of Pieces per Corner[/TD]
[TD]QTY/January[/TD]
[TD]QTY/February[/TD]
[TD]QTY/March[/TD]
[TD]QTY/April[/TD]
[TD]QTY/May[/TD]
[TD]QTY/June[/TD]
[TD]QTY/July[/TD]
[TD]QTY/August[/TD]
[TD]QTY/September[/TD]
[TD]QTY/October[/TD]
[TD]QTY/November[/TD]
[TD]QTY/December[/TD]
[/TR]
[TR]
[TD="align: center"]Auto.[/TD]
[TD="align: center"]S123[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]Insert[/TD]
[TD="align: center"]CNMG 432 PM 4325[/TD]
[TD="align: center"]16392[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]36[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]28[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]Auto.[/TD]
[TD="align: center"]S124[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]Insert[/TD]
[TD="align: center"]WNMG 432-WMX 4215[/TD]
[TD="align: center"]17339[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]150[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]47[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]53[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]0[/TD]
[/TR]
</tbody>[/TABLE]




So, i need to take this table and unpivot it into a format like this..

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Stock #[/TD]
[TD]Item Code[/TD]
[TD]Part #[/TD]
[TD]Date[/TD]
[TD]Sum of Month[/TD]
[TD]Min of Month[/TD]
[TD]Max of Month[/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]Jan[/TD]
[TD]36[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]Feb[/TD]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]Mar[/TD]
[TD]28[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]Apr[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]May[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]Jun[/TD]
[TD]32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]Jul[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]Aug[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]Sep[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]Oct[/TD]
[TD]40[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]Nov[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16392[/TD]
[TD]CNMG 432 PM 4325[/TD]
[TD]S123[/TD]
[TD]Dec[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]Jan[/TD]
[TD]60[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]Feb[/TD]
[TD]53[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]Mar[/TD]
[TD]47[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]Apr[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]May[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]Jun[/TD]
[TD]53[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]Jul[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]Aug[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]Sep[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]Oct[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]Nov[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17339[/TD]
[TD]WNMG 432-WMX 4215[/TD]
[TD]S124[/TD]
[TD]Dec[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Now the tricky part is..
1) it needs to be a Macro or other means because it will be used by many people who wont have PowerPivot or any other type of add on. So it needs to be able to run on a stock 2013 Excel
2) It needs to be dynamic because the data table will continue to grow
3) the Date column needs to be an actual date so i can then use that in the pivot table ill be making with this to sort by months.

* the min and max columns will be formulas based on sum of month

Now that's just my thinking of how to tackle this in order to get a pivot table that consolidates all the inserts and will output the totals per month.

Any help on this would be greatly appreciated as I'm not strong on creating Macros, and not sure how else to go about this.
 
Sort by date Sort by votes
Create another sheet and then Use PowerQuery to transform the data to how you would like it.
 
Upvote 0

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Thing is we don't have powerquery on our machines at work, and multiple people have to use this file. we are running just a bone stock excel 2013
 
Upvote 0
If its authored by someone with PowerQuery and then distributed for reading, the receivers wouldn't need the PQ add-in.
In lieu of that, there is a Macro floating around that does Unpivot, and some modification would be needed to provide a "refresh".
(If someone knows the correct link to that post in this forum....)
As long as the columns remain the same, you shouldn't have any problem using the range .CurrentRegion


Excel 2013... Hate that there were the hidden editions that separated which users could/could not use those add-ins. 2016 adds PQ to all. So happy about that.
 
Upvote 0
SpillerBD said:
If its authored by someone with PowerQuery and then distributed for reading, the receivers wouldn't need the PQ add-in.
In lieu of that, there is a Macro floating around that does Unpivot, and some modification would be needed to provide a "refresh".
(If someone knows the correct link to that post in this forum....)
As long as the columns remain the same, you shouldn't have any problem using the range .CurrentRegion


Excel 2013... Hate that there were the hidden editions that separated which users could/could not use those add-ins. 2016 adds PQ to all. So happy about that.
Click to expand...

hmm true, that's a good point. However i wont be the only one editing the table that needs to be un-pivoted, so i would have to go in after an edit and re-un-pivot.
Yeah agreed about 2013, for a feature that can be greatly advantages, why hide something like that lol. i tried to push for our company to upgrade to 2016, but to no avail sadly.




SpillerBD said:
Found atleast one of the post/threads with the base code.
https://www.mrexcel.com/forum/excel-questions/783577-unpivot-table.html?highlight=unpivot+macro
Click to expand...


Interesting, this may be what I've been looking for. ill have to do some testing and see how i can add it to a refresh button or something to make it somewhat dynamic. Thanks a bunch for finding this!
 
Upvote 0
So I've played around with that macro a bit, and I'm not sure if it'll accomplish what I'm looking to do. It runs well and unpivotes data like how i would want it, but it doesn't seem like there's a way for it to be unpivoted into a table. So it doesn't look like i could create a pivot table that would be driven off of this unpivoted data. i would have to create a new pivot table each time i unpivot data.

Great chunk of code though that i will definitely save for the future.

So close, but yet so far it seems lol.
 
Upvote 0
The resultant range would always be "new" data. If the fields do not change, you should be able to hard code that part of the resultant area, and then redefine the source range for the pivot table at the end of the un-pivot procedure.
The Target area may even stay as a Table if the Header Row isn't touched.
 
Upvote 0
ya so ive been playing around with it, and im getting pretty close actually! im to the point where i have the unpivot data go into a table on another page and then a pivot table from that, so i can get it to work. however the macro code was set up for data that starts in cell A1, where as my data starts in cell C9.

you wouldnt be able to figure out what has to be changed in order to get that to work would you? heres the code:


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

    w2.Select 'ive added this chunk to delete previous data
        Rows("4:4").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Delete Shift:=xlUp
    w1.Select


    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
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
unpivotting a table with macro
Replies
2
Views
6K
Mechixx
M
H
  • Question Question
Calculating avg. prices across specific hours of the year from a dataset using PivotTables?
Replies
0
Views
550
helnuma2k
H
M
  • Question Question
Grouping Columns in Pivot Table and use Timeline/Slicer to select group
Replies
1
Views
1K
Mechixx
M
M
  • Question Question
EXTRAPOLATE DATA FROM A TABLE!!!
Replies
6
Views
1K
JamesCanale
JamesCanale
N
  • Question Question
Combine data from ALL worksheets in different workbook
Replies
0
Views
527
nicars7
N

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

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