Dynamically unpivot data from a table to a new one

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"

Dept.Part #Op #Type of ItemItem CodeStock ## of Corners# of Pieces per CornerQTY/JanuaryQTY/FebruaryQTY/MarchQTY/AprilQTY/MayQTY/JuneQTY/JulyQTY/AugustQTY/SeptemberQTY/OctoberQTY/NovemberQTY/December
Auto.S12310InsertCNMG 432 PM 432516392425036322800320004000
Auto.S12420InsertWNMG 432-WMX 42151733941506053470053000000

<tbody>
</tbody>




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

Stock #Item CodePart #DateSum of MonthMin of MonthMax of Month
16392CNMG 432 PM 4325S123Jan36
16392CNMG 432 PM 4325S123Feb32
16392CNMG 432 PM 4325S123Mar28
16392CNMG 432 PM 4325S123Apr0
16392CNMG 432 PM 4325S123May0
16392CNMG 432 PM 4325S123Jun32
16392CNMG 432 PM 4325S123Jul0
16392CNMG 432 PM 4325S123Aug0
16392CNMG 432 PM 4325S123Sep0
16392CNMG 432 PM 4325S123Oct40
16392CNMG 432 PM 4325S123Nov0
16392CNMG 432 PM 4325S123Dec0
17339WNMG 432-WMX 4215S124Jan60
17339WNMG 432-WMX 4215S124Feb53
17339WNMG 432-WMX 4215S124Mar47
17339WNMG 432-WMX 4215S124Apr0
17339WNMG 432-WMX 4215S124May0
17339WNMG 432-WMX 4215S124Jun53
17339WNMG 432-WMX 4215S124Jul0
17339WNMG 432-WMX 4215S124Aug0
17339WNMG 432-WMX 4215S124Sep0
17339WNMG 432-WMX 4215S124Oct0
17339WNMG 432-WMX 4215S124Nov0
17339WNMG 432-WMX 4215S124Dec0

<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.
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
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
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)

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

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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