BOM to Part List

[yaniv9612]

Hi everyone,

So I am new to the forum but been following it for a few years now.
So I've been having trouble finding a solution for this problem on this forum, though I am pretty sure there is one.
I have this Bill Of Materials(BOM), which consists of 4 columns - Level(1..9), Part Number, Description and Quantity.
I need to convert the BOM to a part list so each part in the BOM will appear once, and the Quantity shown for it will be the sum of all of it appearances throughout the BOM, including of course multiplying it by it's parent's qty and so on.
I attached a file with an example with it, though the real BOM I need to convert is about a 1000 lines long and with levels up to 20.

Thank you very much!

[CAntosh]

You can do this without VBA, if I'm understanding your request correctly.

In G3, array-entered (confirm with Ctrl + Shift + Enter instead of Enter):
=IFERROR(INDEX($B$3:$B$29,SMALL(IF(COUNTIF($G$2:$G2,$B$3:$B$29)=0,ROW($B$3:$B$29)),ROW(1:1))-2),"")

In H3, not array entered:
=IF($G3="","",INDEX($C$3:$C$29,MATCH($G3,$B$3:$B$29,0)))

In I3, non-array:
=IF($G3="","",SUMIF($B$3:$B$29,G3,$D$3:$D$29))

Then fill down as far as you think you'll need. The benefit of this approach is that you can change the range from e.g. B3:B29 to e.g. B3:B10000 in each formula, fill down well beyond the number of rows you need in G:I, and it'll automatically update every time you add new rows to your BOM section. Take a look at the attachment and see if you think it'll work:

[yaniv9612]

Hi CAntosh,

Thanks for the answer, however the results on your file are wrong. For instance the total qty for AA should be 27. It has 1 unit on level 1, unit which is part of the DD assembly (which of requires 2 units), so now the total sum is 3, and there is also 2 units in the QQ assembly which requires 12 units of, so that is an additional 24. so the sum is 27.
Thanks.

[CAntosh]

Thanks for the answer, however the results on your file are wrong.

Yeah, when I saw shg's response, I figured there was something in the levels that I clearly didn't understand. Apologies. After picking through the math of shg's formula, though, I think I get the basic logic of it now. In the future, it's helpful to include a few manually calculated "expected results" so that the fools among us who are doing it wrong can tell that we're doing something wrong. In the end, though, I'm glad that you were able to get a solution that worked, and I'm equally pleased to have learned something new. Good luck!

[shg]

	B	C	D	E	F	G	H	I	J	K
2	Level	P/N	Description	Qty.	Ext Qty		P/N	Tot Qty
3	1	AA		1	1		AA	27		F3 and down:=E3 * IF(B3=1, 1, LOOKUP(9E+307, F$2:F2 / (B$2:B2 = B3-1)))
4	1	BB		1	1		BB	1		I3 and down:=SUMIF($C$3:$C$29, H3, $F$3:$F$29)
5	1	CC		1	1		CC	1
6	1	DD		2	2		DD	2
7	2	EEE		1	2		EEE	8
8	2	AA		1	2		FFF	32
9	2	FFF		16	32		GGG	1,024
10	3	GGG		32	1,024		HH	32
11	3	HH		1	32		III	32
12	4	III		1	32		JJJ	32
13	4	JJJ		1	32		KK	320
14	5	KK		10	320		LL	160
15	4	LL		5	160		MM	192
16	3	MM		6	192		NN	32
17	3	NN		1	32		OO	2
18	2	OO		1	2		PP	1
19	1	PP		1	1		QQ	12
20	1	QQ		12	12		RR	36
21	2	RR		3	36		SS	24
22	2	SS		2	24		TT	1
23	2	AA		2	24		UU	2
24	1	TT		1	1		WW	1
25	2	UU		2	2		YY	4
26	3	EEE		3	6		ZZ	2
27	3	ZZ		1	2
28	4	YY		2	4
29	1	WW		1	1

Col H is the part numbers of col C sorted with duplicates removed.

[yaniv9612]

Hi Shg.

First of all- WOW, I never thought this could be achieved without using VB and some sort of loop. Could you please explain how the formulas work?
Also- If I still want this to be a part of a vb macro, as I want it to do some other things beside converting the BOM, what will be the loop I will need?
Thanks again!

[shg]

The formula in col F calculates the extended quantity of the current assembly as the current quantity times the quantity of the next higher assembly.

[shg]

In response to your PM:

In this expression,

LOOKUP(9E+307, F$2:F2 / (B$2:B2 = B3-1))

... there is a lookup vector

F$2:F2 / (B$2:B2 = B3-1)

... formed by an array of fractions in which the numerators are assembly quantities, and the denominators are logical values that are TRUE when the assembly is exactly one level above the current assembly, and FALSE otherwise. When logical expressions are used in an arithmetic expression, Excel coerces TRUE to 1 and FALSE to 0, so the vector is a mix of numbers (quantities of assemblies one level up) and #DIV/0! errors (assemblies that are not one level up).

It is the nature of LOOKUP that it will only return a match of like type, so the errors are ignored.

Among those numbers, the last one in the vector is the one we want; the quantity of the immediate higher assembly.

The LOOKUP function does a binary search, which requires the assumption that the vector is in ascending order, even though here it's in no order at all. But since the lookup value (9E+307) is larger than any number in the vector, LOOKUP keeps going higher and higher, and ultimately returns the last number, which is just what we wanted.

Watch it evaluate.

Please keep your questions in the forum.