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.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Sort by date Sort by votes
do you and the workers have MSACCESS, a simple front end would allow for stock ID's and dates, then from the backend you would generate your tables, with calculations by month being done in code. It could be built as a webpage (with the right skills and development software)
 
Upvote 0
mole999 said:
do you and the workers have MSACCESS, a simple front end would allow for stock ID's and dates, then from the backend you would generate your tables, with calculations by month being done in code. It could be built as a webpage (with the right skills and development software)
Click to expand...

I think we do have MSACCESS but I've never used it, and wouldn't have a clue how to set it up for this. I would be open to the idea though and can pick things up fairly quickly if it means an efficient method of being able to do this.
And i don't think a web-page would be possible lol as that's even further away from my abilities and software available.
I was hoping there would be a simple way in excel to do it with VBA but im beginning to think there isn't..
 
Upvote 0
Could this be done with a INDEX/MATCH type formula perhaps for what im trying to achieve?
Or if there's any other way someone can think of, even if i have to change the format of my tables or something, as long as i can arrive at the second table format somehow.
 
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
549
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,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

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