Dynamically unpivot data from a table to a new one

[Mechixx]

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"

Dept.	Part #	Op #	Type of Item	Item Code	Stock #	# of Corners	# of Pieces per Corner	QTY/January	QTY/February	QTY/March	QTY/April	QTY/May	QTY/June	QTY/July	QTY/August	QTY/September	QTY/October	QTY/November	QTY/December
Auto.	S123	10	Insert	CNMG 432 PM 4325	16392	4	250	36	32	28	0	0	32	0	0	0	40	0	0
Auto.	S124	20	Insert	WNMG 432-WMX 4215	17339	4	150	60	53	47	0	0	53	0	0	0	0	0	0

<tbody>
</tbody>

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

Stock #	Item Code	Part #	Date	Sum of Month	Min of Month	Max of Month
16392	CNMG 432 PM 4325	S123	Jan	36
16392	CNMG 432 PM 4325	S123	Feb	32
16392	CNMG 432 PM 4325	S123	Mar	28
16392	CNMG 432 PM 4325	S123	Apr	0
16392	CNMG 432 PM 4325	S123	May	0
16392	CNMG 432 PM 4325	S123	Jun	32
16392	CNMG 432 PM 4325	S123	Jul	0
16392	CNMG 432 PM 4325	S123	Aug	0
16392	CNMG 432 PM 4325	S123	Sep	0
16392	CNMG 432 PM 4325	S123	Oct	40
16392	CNMG 432 PM 4325	S123	Nov	0
16392	CNMG 432 PM 4325	S123	Dec	0
17339	WNMG 432-WMX 4215	S124	Jan	60
17339	WNMG 432-WMX 4215	S124	Feb	53
17339	WNMG 432-WMX 4215	S124	Mar	47
17339	WNMG 432-WMX 4215	S124	Apr	0
17339	WNMG 432-WMX 4215	S124	May	0
17339	WNMG 432-WMX 4215	S124	Jun	53
17339	WNMG 432-WMX 4215	S124	Jul	0
17339	WNMG 432-WMX 4215	S124	Aug	0
17339	WNMG 432-WMX 4215	S124	Sep	0
17339	WNMG 432-WMX 4215	S124	Oct	0
17339	WNMG 432-WMX 4215	S124	Nov	0
17339	WNMG 432-WMX 4215	S124	Dec	0

<tbody>
</tbody>

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.

 

[jak82]

Create another sheet and then Use PowerQuery to transform the data to how you would like it.

 

[Mechixx]

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

 

[SpillerBD]

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.

 

[SpillerBD]

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

 

[Mechixx]

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.

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.

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

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!

 

[Mechixx]

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.

 

[SpillerBD]

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.

 

[Mechixx]

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:

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