If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Cummaltive/Summarised Item total on a different product vlookup, depending on the description on the operation
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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.592 / Virus Database: 375 - Release Date: 19/02/04 |
#2
|
|||
|
|||
Cummaltive/Summarised Item total on a different product vlookup,depending on the description on the operation
Have you tried summarizing the data with a pivot table? There are
instructions and links to information he http://www.peltiertech.com/Excel/Pivots/pivotstart.htm John Colling wrote: 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.592 / Virus Database: 375 - Release Date: 19/02/04 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#3
|
|||
|
|||
Cummaltive/Summarised Item total on a different product vlookup, depending on the description on the operation
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 "Debra Dalgleish" wrote in message ... Have you tried summarizing the data with a pivot table? There are instructions and links to information he http://www.peltiertech.com/Excel/Pivots/pivotstart.htm John Colling wrote: 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 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.592 / Virus Database: 375 - Release Date: 19/02/04 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.592 / Virus Database: 375 - Release Date: 18/02/04 |
Thread Tools | |
Display Modes | |
|
|