Help with a macro/pivot table

lwilt

Board Regular
Joined
May 16, 2013
Messages
187
I have a file that has 2 parts to it. In the first sheet it has a list of item quantity break codes with the different breaks each code has. Each break corresponds to a different price but that isn't relevant here. The second sheet has a list of data that includes item number, break code, year, qty, and sales.

I am trying to find a way break the data down by year so that 2013 is next to 2014 and so on. Within each year each item number would have the number of orders counted and sales summed based on the items qty that occurred in each order and matched into the quantity break table.

I don't know how to upload a file here but I included a link to another site where I created a thread there and was able to upload the example file.

Can a macro sum multiple things to multiple criteria?

Not sure if a macro or pivot table could do this because there are 190 different qty break codes that each have their own unique set of 7 breaks so it seems to complicated for a pivot table.

Please Help!!!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
let me try to copy it from the example file on the other site where the link is posted in the previous post.

item quantity breaks for quantity ordered in each order example:
[TABLE="width: 545"]
<colgroup><col><col span="7"></colgroup><tbody>[TR]
[TD]Break Number[/TD]
[TD]B1[/TD]
[TD]B2[/TD]
[TD]B3[/TD]
[TD]B4[/TD]
[TD]B5[/TD]
[TD]B6[/TD]
[TD]B7[/TD]
[/TR]
[TR]
[TD="align: right"]121[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]169[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]21[/TD]
[/TR]
[TR]
[TD="align: right"]201[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Example of data
[TABLE="width: 459"]
<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]desc[/TD]
[TD]Break Number[/TD]
[TD]order number[/TD]
[TD]order date[/TD]
[TD]Qty[/TD]
[TD]Sales[/TD]
[/TR]
[TR]
[TD]AA121[/TD]
[TD][/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]1/25/2014[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]AA121[/TD]
[TD][/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]122[/TD]
[TD="align: right"]1/26/2014[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD]AB261[/TD]
[TD][/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]234[/TD]
[TD="align: right"]3/6/2014[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]2000[/TD]
[/TR]
[TR]
[TD]AB261[/TD]
[TD][/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]6667[/TD]
[TD="align: right"]7/5/2015[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]232[/TD]
[/TR]
[TR]
[TD]AA121[/TD]
[TD][/TD]
[TD="align: right"]121[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]2/12/2015[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]566[/TD]
[/TR]
[TR]
[TD]HH774[/TD]
[TD][/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]98[/TD]
[TD="align: right"]9/21/2014[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]977[/TD]
[/TR]
[TR]
[TD]AB261[/TD]
[TD][/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]74747474[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]121[/TD]
[/TR]
[TR]
[TD]HH774[/TD]
[TD][/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]885[/TD]
[TD="align: right"]12/1/2014[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]141[/TD]
[/TR]
[TR]
[TD]HH774[/TD]
[TD][/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]8/14/2015[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]252[/TD]
[/TR]
[TR]
[TD]HH774[/TD]
[TD][/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]31[/TD]
[TD="align: right"]8/17/2015[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]252[/TD]
[/TR]
</tbody>[/TABLE]



Final should look like
[TABLE="width: 1809"]
<colgroup><col><col><col span="2"><col span="10"><col span="10"><col span="4"><col><col></colgroup><tbody>[TR]
[TD]Item[/TD]
[TD]Desc[/TD]
[TD]14o break1[/TD]
[TD]14s break1[/TD]
[TD]14o break2[/TD]
[TD]14s break2[/TD]
[TD]14o break3[/TD]
[TD]14s break3[/TD]
[TD]14o break4[/TD]
[TD]14s break4[/TD]
[TD]14o break5[/TD]
[TD]14s break5[/TD]
[TD]14o break6[/TD]
[TD]14s break6[/TD]
[TD]14o break7[/TD]
[TD]14s break7[/TD]
[TD]15o break1[/TD]
[TD]15s break1[/TD]
[TD]15o break2[/TD]
[TD]15s break2[/TD]
[TD]15o break3[/TD]
[TD]15s break3[/TD]
[TD]15o break4[/TD]
[TD]15s break4[/TD]
[TD]15o break5[/TD]
[TD]15s break5[/TD]
[TD]15o break6[/TD]
[TD]15s break6[/TD]
[TD]15o break7[/TD]
[TD]15s break7[/TD]
[/TR]
[TR]
[TD]AA121[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$28[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$566[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]AB261[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$121[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$2,000[/TD]
[TD] [/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$232[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]HH774[/TD]
[TD][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$141[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]$997[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]$504[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
It would be easiest to use a helper cell to essentially correlate the "Break Number" to the break grouping (B1, B2 etc)
Then organize your Pivot Table with Items in your Rows and Columns by Date(Grouped by Year) and Break grouping.
Set for Tabular mode and No subtotals for Rows or Columns.
 
Upvote 0
The only problem with doing it that way is that there is ~190 different break codes so I would have to make 190 different pivot tables then combine the information back together. Doing that many pivot tables would take way to much time.
 
Upvote 0
The only problem with doing it that way is that there is ~190 different break codes so I would have to make 190 different pivot tables then combine the information back together. Doing that many pivot tables would take way to much time.
1 Pivot Table.... 7 Break Codes x Break Number x Years.

Regardless the correlation between the Break Codes and The Break Number must be made for the analysis you wish to display. The Table that has that would be best if it were transposed for Index(Match method for the lookup.
 
Upvote 0
1 Pivot Table.... 7 Break Codes x Break Number x Years.

Regardless the correlation between the Break Codes and The Break Number must be made for the analysis you wish to display. The Table that has that would be best if it were transposed for Index(Match method for the lookup.

I don't follow your response...how would you put an index match in a pivot table
 
Upvote 0
I don't follow your response...how would you put an index match in a pivot table
It would be in the Source Data/Table.
While you can do some Calculations (Fields and Lables) Lookup functions are not available. (PowerPivot probably could though.)

The data for the organization you want must first be available in the Source Data for the Pivot Table, otherwise you will not be able to accomplish the goal you have stated.
 
Upvote 0
I just did it and it won't work so I have no idea how you think it will sum all the different break codes into one output...
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

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