Help with a macro/pivot table

[lwilt]

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

 

[oldbrewer]

can you make up a pretend spreadsheet with say 3 products for 2013 and 2014 ?

 

[lwilt]

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]

 

[lwilt]

Is there a way to upload an example file to make it easier to understand?

 

[SpillerBD]

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.

 

[lwilt]

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.

 

[SpillerBD]

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.

 

[lwilt]

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

 

[SpillerBD]

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.

 

[lwilt]

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