Excel - Divide product demand into components

[spartain]

Hello,
I have a spreadsheet that calculates inventory supermarket levels for a list of products. However, some of these items are composed of multiple components which are stocked (rather than the product itself). So, I need to structure a sheet that will translate stock levels for a product list into the stock levels for the component list.

For example, this is the information I have now:
Product Components Supermarket Qty.
A a,b 3
B b 2
C c,d 1
D d 5

And I want to end up with:
Component Supermarket Qty.
a 3
b 5
c 1
etc.

Can anyone help me with a way of doing this? It doesn't seem like it should be that big of a deal, but I can't get my brain started...

Thanks!

 

[SkipVought]

Hi,

How many rows have compound components?

Are there ever more than 2 components per row?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[spartain]

Skip,
50 of the 120 products have compound components, but none have more than 2 components.
Thanks,
Seth

 

[SkipVought]

1) AutoFilter on -- Does Not Contain [Comma]

2) copy all visible data

3) on a separate sheet select A1 and Edit/Paste Special - VALUES

4) AutoFilter on -- Contains [Comma]

5) copy all visible data

6) in the NEXT empty cell on the new sheet, Edit/Paste Special - VALUES

7) in the SECOND PASTED ROW RANGE, select the Supermarket Qty. VALUES and Insert - Shift Cells RIGHT

8) in the SECOND PASTED ROW RANGE, select the Components VALUES, Data/Text to Columns - DELIMITER and select the [Comma] delimiter. You now have the components in 2 columns.

It should be obvious what to do at this point.

VOLA!

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[spartain]

Okay Skip,
I'm not trying to be dense, but it's the next steps that have me confused as to what to do. So I have the components separated into different columns:
Product Comp1 Comp2 Qty
A a b 3
B b 2
C c d 5
D d 1

What I need is a component list that lists the component quantity needed to satisfy the product quantities (kind of an "Bill of Materials" calculation):

Component Qty
a 3
b 5
c .
d .

It's this next step, the formula for the "Qty" column, that's giving me trouble.
I'm looking for a formula that I can use in the "Qty" column of the component list that will determine the component demand for the various products that require that component(see Component "b" in the example). I know I can enter the formula in manually do it (Qty for b = Qty. for A + Qty. for B)--but I'd rather not have to do it manually for all 50 items, and I'd like to it to be automatic so that it's easier to maintain.

Any ideas on how to do that?
Thanks again,
Seth

 

[SkipVought]

Seth,

You did NOT go by the numbers. The step I gave you would NOT have resulted in
[tt]
Product Comp1 Comp2 Qty
A a b 3
B b 2
C c d 5
D d 1
[/tt]
rather
steps 1-3
[tt]
Product Comp1 Qty
B b 2
D d 1
[/tt]
steps 4-8
[tt]
Product Comp1 Qty
A a b 3
C c d 5
[/tt]
then you should be able to figgure out how to finish it off.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[spartain]

Skip,
You're right, the response I posted did not change the order of the parts--and I did see that that's what your procedure resulted in. However, I would like to keep from completely resorting the parts (they are sorted by product family and I'd like to keep them together for readability). I'm also not sure how resorting the parts as per your instructions makes the task I have in mind easier to accomplish. A little clarification on how to proceed from here (without manually entering in the "sum" equation for each component..)?
Thanks,
Seth

 

[SkipVought]

Just resort to re-sorting!

BTW, the QTY for b is NOT 5 taken with PRODUCT!

using the posted process. I did this in one minute
[tt]
Product Components Supermarket Qty.
A a 3
A b 3
B b 2
C c 1
C d 1
D d 5
[/tt]
Then use SUMPRODUCT to sume whatever combinations you want.



Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[spartain]

The quantity for component "b" IS 5! If we are making 3 of product A and 2 of product B and both product A and B require 1 b, then we need to make 5 b's.

 

[SkipVought]

So add 'em up
[tt]
=sumproduct((B2:B7="b")*(C2:C7))
[/tt]
or use the PIVOT TABLE wizard to sum on conponents.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[spartain]

Okay!
Thanks for your help!