Shopping list/ Bill of materials

[dimbasco]

I guess my first post was way too long and complex.. I'll give it one more shot:

I have a list of Products:

Oranges
Apples
Multipack - 1
Bananas
Pears
Multipack - 2

I can sell Oranges, Apples, Bananas and Pears individually.
Multipacks contains the following:

Book1
	A	B	C	D	E
1		Oranges	Apples	Bananas	Pears
2	Multipack-1	2	2
3	Multipack-2		1	2
4	Multipack-3	1	1	1	1
Sheet1

Customer places an order for:
10 units Oranges
5 units Bananas
2 units Multipack - 2
1 unit Multipack - 1

IS there a way to calculate exactly how many of the following I need?
Oranges
Apples
Bananas
Pears

 

[steve case]

Here's how I would do it:

................A..............B...............C..............................D
1..............Product.....Qty............Formula.....................Total
2..............Oranges....10.............=B3+(B9)...................10
3..............Apples.......................=B4+(B7*2)+B8+B9.....4
4..............Bananas......5.............=B5+(B8*2)+B9..........9
5..............Pears.........................=B6+B9......................0
6..............Multi 1.........1
8..............Multi 2.........2
9..............Multi 3

Column D is the formulas calcualted result

 

[HalfAce]

The answer to this seems so obvious it makes me think I must be missing something.
What would I be missing if I suggested you simply put a "totals" row at the bottom of each column and use the formula(s)
=Sum(B2:B4)
=Sum(C2:C4)...etc.

I gotta say, if it were that simple you probably wouldn't be here asking...

Dan

Edit:
I guess some better questions would be: Does your data table really look like that, or is that a simple example for our benefit?
Are you wanting to simply enter "Multipack - 1" and have it already know what that consists of? (Will the values be put on the sheet somewhere, somehow?)

 

[just_jon]

Dan, I think it's a bit more complex: Here's my take.

First, you need to look at each item in the order and determine if it is a stand-alone item or consists of multiple items itself.

For those which are stand alones, the item qty is simple incremented by the buy quantity.

For the others, you must then determine which "sub-assemblies" are involved. Then, for each sub-assembly, multiple it's qty-per-end item times the qty ordered, then add that number to the cummulative sub-item's total.

The whole thing, if it's like a materials database, probably ought to be in Access.

 

[Yogi Anand]

Hi dimbasco:

I had seen your original question that you have since deleted -- I don't think that was too complex, it was just too long. Anyway, let us have a look at the following ...

Book1
	A	B	C	D	E	F	G
1				Oranges	Apples	Bananas	Pears
2			Multipack-1	2	2
3			Multipack-2		1	2
4			Multipack-3	1	1	1	1
5
6				Oranges	Apples	Bananas	Pears
7		alacarte	Qty	10		5
8		Multipack-1	1	2	2	0	0
9		Multipack-2	2	0	2	4	0
10		Multipack-3		0	0	0	0
11		TotalOrdered		12	4	9	0
12
Sheet7

formula in cell D8 is ... =VLOOKUP($B8,$C$1:$G$4,COLUMNS($A:B))*$C8
this is then copied across to column G and down to row 10

formula in cell D11 is ... =SUM(D7:D10)
this is then copied across to column G

I hope this helps!

 

[Domenic]

Try the following...

1) Set up the following on Sheet2:

Product	Qty
Oranges	10
Apples	
Bananas	5
Pears	
Multipack-1	1
Multipack-2	2
Multipack-3

2) Then, enter the following formula in C2 and copy down to C5:

=B2+SUMPRODUCT((Sheet1!$B$2:$E$4)*(Sheet1!$B$1:$E$1=A2)*($B$6:$B$8))

Hope this helps!

 

[fairwinds]

Hi,

One more option:

=SUMIF($H$2:$H$9,B7,$G$2:$G$9)+SUMPRODUCT(SUMIF($H$2:$H$9,$A$2:$A$4,$G$2:$G$9)*B2:B4)

in B8 and drag right.

Book1
	A	B	C	D	E	F	G	H
1		Oranges	Apples	Bananas	Pears		Quantity	Item
2	Multipack-1	2	2				10	Oranges
3	Multipack-2		1	2			5	Bananas
4	Multipack-3	1	1	1	1		2	Multipack-2
5							1	Multipack-1
6
7	Toship	Oranges	Apples	Bananas	Pears
8		12	4	9	0
9
Sheet1

 

[HalfAce]

Hi Jon (and all),
Thanks for your take on this.
What I was driving at (would have eventually) basically revolved around how the data was layed out on the sheet. Using some VLOOKUPs like Yogi's done, and then ending up with something similar to what Fairwinds displayed was what I had in mind. (Allowing one to just sum up the columns.)

I shall now sit back & watch how those more capable than I handle it.

Dan

 

[dimbasco]

Thanks guys... Actually I oversimplified the problem. Most of you have posted solutions that will work - only if my problem would be this simple. I was using Vlookups to do the same. (Thx Yogi, Fairwinds, Halface, justJon, Domenic, StAcase... all of you .. .)

I think the one person that caught on to the depth of the problem is Just_Jon. A Materials database/ MRP is what I am looking to build.

Within Excel my Issue is that I have a table that looks like shown below.

The Data is monthly forecasts for given SKU's. What I have is some of these SKU's are made up of two or more component SKU's (also in the same list like the Multi-packs in my post). For example:
A02281 consists of 2 units of the following: A02276, A02277,A02278, A02279.

Book1
	A	B	C	D	E
1	DmdUnit	2005P2	2005P3	2005P4	2005P5
2	A02276	6541	4014	5508	4534
3	A02277	5528	3144	4567	3558
4	A02278	4759	2724	4157	3093
5	A02279	5696	3258	4683	3662
6	A02280	0	0	0	0
7	A02281	9732	1177	1203	1136
Sheet1

I have a seperate master list with all the Multipack Bill of Materials with Components SKU's and quantities and used Vlookup to match and figure out forecasts for sub components (in the multi-packs). My biggest issue was all this is manual. I need to Vlookup multipack demand - translate into components - and then copy back translated demand into a sheet with just the single (individual not multipack) SKU demand - which then needs a pivot table to add up! (sheesh typing this was almost as painful).

Jon - I tried access - but am not very good. Any suggestions? I am more than willing to look at Excel if there is a solution...

Thx for your support and ideas fellow Excellers.

 

[Yogi Anand]

Hi Dimbasco:

Since you are the architect of the project, you know what the project is all about, its makeup, its complexity, who is your audience, and so on. Viewers here can only get a snapshot of the project through your description.

ACCESS or EXCEL is always an interesting issue. Unless you are dealing with real huge data, you can structure your data using EXCEL's guidelines for working with datbases, you can also structure your data in Tables and subTables, versus working with a flat file structure and leverage your knowledge of EXCEL as well as its enormpus versatility.