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:

Break Number	B1	B2	B3	B4	B5	B6	B7
121	1	2
169	1	2	3	6	9	12	21
201	1	5	12

<colgroup><col><col span="7"></colgroup><tbody>
</tbody>

Example of data

Item	desc	Break Number	order number	order date	Qty	Sales
AA121		121	121	1/25/2014	2	14
AA121		121	122	1/26/2014	2	14
AB261		169	234	3/6/2014	12	2000
AB261		169	6667	7/5/2015	1	232
AA121		121	1002	2/12/2015	7	566
HH774		201	98	9/21/2014	9	977
AB261		169	74747474	1/1/2014	2	121
HH774		201	885	12/1/2014	1	141
HH774		201	29	8/14/2015	6	252
HH774		201	31	8/17/2015	6	252

<colgroup><col span="2"><col><col><col><col><col></colgroup><tbody>
</tbody>

Final should look like

Item

Desc

14o break1

14s break1

14o break2

14s break2

14o break3

14s break3

14o break4

14s break4

14o break5

14s break5

14o break6

14s break6

14o break7

14s break7

15o break1

15s break1

15o break2

15s break2

15o break3

15s break3

15o break4

15s break4

15o break5

15s break5

15o break6

15s break6

15o break7

15s break7

AA121

2

$28

1

$566

AB261

1

$121

1

$2,000

1

$232

HH774

1

$141

1

$997

2

$504

<colgroup><col><col><col span="2"><col span="10"><col span="10"><col span="4"><col><col></colgroup><tbody>
</tbody>

 

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