Cummaltive/Summarised Item total on a different product vlookup, depending on the description on the

[John Colling]

Sorry this ones a bit long.

Background:

I have spreadsheet which contains a sheet with all the raw data and another
for the summary sheet.

The summary sheet uses a vlookup to get the data from the raw data by using
the "product" as a key driver.
I have created a "unqiue Ref" by using the "product" and "Operation"
combined, this returns all lines(rows) associated with the operation of a
product.

Extract in excel- B1 contains the "Product"

COLUMN A COLUMN B COLUMN C COLUMN D COLUMN E COLUMN F COLUMN G
ROW1 "Cake"
ROW2 Unqiue Ref Product Operation Description Cost Operation
Description

ROW3 Cake 1 cake 1 Setup 1 10 Operation 1
ROW4 Cake 2 cake 2 Operation 1 150 Operation 1
ROW5 Cake 3 cake 3 Material 1 10
ROW6 Cake 4 cake 4 Material 2 20
ROW7 Cake 5 cake 5 Material 3 30
ROW8 Cake 6 cake 6 Material 4 40
ROW9 Cake 7 cake 7 Setup 2 10 Operation 2
ROW10 Cake 8 cake 8 Operation 2 200 Operation 2
ROW11 Cake 9 cake 9 Setup 3 10 Operation 3
ROW12 Cake 10 cake 10 Operation 3 300 Operation 3


The problem that I have is that, each product has varying rows, some are
only 10 rows of data whilst others can be up to 50 lines, operations/
material vary on products and the order changes as some require operation 3
before operation 2. Just to throw another twist for example operation 2 has
different descriptions , i.e. oven 1, oven 2;

This makes it difficult to use a set of fixed descriptions for a "sumif
function" especially when operations don't follow that order necessarily.

What I'm trying to get to is the following summary to the left of the detail
(as above) is a summary of the operation step cost and a Cumulative cost
Operation
Description Total Cumulative
Material 100 100
Operation 1 160 260
Operation 2 210 470
Operation 3 310 780

I don't know what to do next, Hope the above makes sense????

Many thanks in advance

JC

 

[Debra Dalgleish]

Have you tried summarizing the data with a pivot table? There are
instructions and links to information here:

http://www.peltiertech.com/Excel/Pivots/pivotstart.htm

 

[John Colling]

Thanks Debra for the pivot table idea, I've spend a few hours on trying to
get this to work on the way that I what. I hit a problem in that the Pivot
table is taking no reference of the operational order.

So have gone back to my orginal idea, is their a formula to do what the
advance filter with unquie records only does, as I think that this would
solve my problem, as I could combine this with a sumif on the unquie
opertion descriptions.

I've tried using =IF(COUNTIF($D$1010,D10)=1,D10,""), but this includes a
blank row when it finds a duplicate, any ideas

Once again thank you for your time.

Can you recommend any goods books ?
formula's
Regards

JC