Need help with a formula

[Jobb1Excel]

Hey, i got a list of products and the components of every product and need a formula to lookup the material number and find all the components of that material number. The list looks like this:

So what i need is: Lets take A40142002 at line 17 as an example: I want the formula to search for A40142002 in the list and find all components (all components of the product has the same "Stykkliste" number, so everything with the number 17378 is components of the product A40142002. So i want the output of the formula to be "A40142002 components: A40142009 SNITTA GAFFELLEGG HØGRE FOR M 20 NO 401 + A40142011 GAFFELHODE FOR STREKKSKRUE M 20 NO 401" and if more components it will list all those aswell based on matching "Stykkliste" numbers.

 

[RobP]

Hi, does this give you what you need ?

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	########				Stykkliste
2				A40140802
3	Stykkliste
4		Stykkliste		Materialnummer	Alt	Bruk	Basiskvarn	BKE	Pos.	Komponent	Materialbetegnelse
5		17370		A00300601	1	1	1	STK	10	A1101008	IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM
6		17372		A40140602	1	1	1	STK	10	A1105006	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM
7		17372					1	STK	20	A1102205	IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM
8		17373		A40140802	1	1	1	STK	10	A1105008	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM
9		17373					1	STK	20	A1102202	IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM
10		17374		A40141002	1	1	1	STK	10	A40141009	SNITTA GAFFELLEGG HØGRE FOR M 10 NO 401
11		17374					1	STK	20	A40141011	GAFFELHODE FOR STREKKSKRUE M 10 NO 401
12		17375		A40141202	1	1	1	STK	10	A40141209	SNITTA GAFFELLEGG HØGRE FOR M 1.2 NO 401
13		17375					1	STK	20	A40141211	GAFFELHODE FOR STREKKSKRUE M 12 NO 401
14		17376		A00300802	1	1	1	STK	10	A3099000	IKKE BRUK EMNER FOR KRAGEPINN 8*3.2MM
15		17377		A40141602	1	1	1	STK	10	A40141609	SNITTA GAFFELLEGG HØGRE FOR M 16 NO 401
16		17377					1	STK	20	A40141611	GAFFELHODE FOR STREKKSKRUE M 16 NO 401
17		17378		A40142022	1	1	1	STK	10	A40142009	SNITTA GAFFELLEGG HØGRE FOR M .20 NO 401
18		17378					1	STK	20	A40142011	GAFFELHODE FOR STREKKSKRUE M .20 NO 401
19		17379		A40142222	1	1	1	STK	10	A40142200	EMNE FOR GAFFEL M 22 NO 401
20		17379					1	STK	20	E_GJENGETAPP	(GJENGETAPP FOR 7/8" og 1" SJ
21		17379					1	STK	30	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
22		17380		A40142402	1	1	1	STK	10	A40142400	EMNE FOR GAFFEL M .24 NO 401/801
23		17380					1	STK	20	E_GJENGETAPP	GJENGETAPP FOR 7/8" og 1" SJ
24		17380					1	STK	30	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
25		17381		A00300902	1	1	1	STK	10	A3099002	PREGA EMNER FOR KRAGEPINN 10*38,5MM
26		17382		A40143222	1	1	1	STK	10	10 A40143200	EMNE FOR GAFFEL 1 1/4" NO 401/801
27		17382					1	STK	20	C_GJENGETAPP	GJENGETAPP FOR 1 1/4" SJ
28		17382					1	STK	30	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
29		17383		A40143822	1	1	1	STK	10	A40143800	EMNE FOR GAFFEL 1 1/2" NO 401/801
30		17383					1	STK	20	A_GJENGETAPP	(GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
31		17384		A00301102	1	1	1	STK	10	A1105011	IKKE BRUK BLANKTRUKKET STÅLST 37-2 11MM
32		17385		A40144502	1	1	1	STK	10	A40144500	EMNE FOR GAFFEL 13/4" NO 401/801
33		17385					1	STK	20	A_GJENGETAPP	GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
34		17386		A00301302	1	1	1	STK	10	A1105012	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 12N./M
35		17387		A40145222	1	1	1	STK	10	A40145200	EMNE FOR GAFFEL.2" NO 401/801
36		17387					1	STK	20	G_GJENGETAPP	GJENGETAPP FOR 2." og 2 1/2" SJ
37
38
39
40		17373	0	A40140802	1	1	1	STK	10	A1105008	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM
41		17373	0	0	0	0	1	STK	20	A1102202	IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM
Sheet1

Cell Formulas
Range	Formula
B40:K41	B40	=LET(styk,XLOOKUP($D$2,D5:D36,B5:B36,"No Parts"),output,IFERROR(FILTER(B5:K36,B5:B36=styk),"no part"),output)
Dynamic array formulas.

 

[RobP]

Or, if you wanted to just choose a few specific columns for your output, you may do it like this: To get desired output, again I type the Material Number I want to use into "D2"

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	########				Stykkliste
2				A40142222
3	Stykkliste
4		Stykkliste		Materialnummer	Alt	Bruk	Basiskvarn	BKE	Pos.	Komponent	Materialbetegnelse
5		17370		A00300601	1	1	1	STK	10	A1101008	IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM
6		17372		A40140602	1	1	1	STK	10	A1105006	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM
7		17372					1	STK	20	A1102205	IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM
8		17373		A40140802	1	1	1	STK	10	A1105008	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM
9		17373					1	STK	20	A1102202	IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM
10		17374		A40141002	1	1	1	STK	10	A40141009	SNITTA GAFFELLEGG HØGRE FOR M 10 NO 401
11		17374					1	STK	20	A40141011	GAFFELHODE FOR STREKKSKRUE M 10 NO 401
12		17375		A40141202	1	1	1	STK	10	A40141209	SNITTA GAFFELLEGG HØGRE FOR M 1.2 NO 401
13		17375					1	STK	20	A40141211	GAFFELHODE FOR STREKKSKRUE M 12 NO 401
14		17376		A00300802	1	1	1	STK	10	A3099000	IKKE BRUK EMNER FOR KRAGEPINN 8*3.2MM
15		17377		A40141602	1	1	1	STK	10	A40141609	SNITTA GAFFELLEGG HØGRE FOR M 16 NO 401
16		17377					1	STK	20	A40141611	GAFFELHODE FOR STREKKSKRUE M 16 NO 401
17		17378		A40142022	1	1	1	STK	10	A40142009	SNITTA GAFFELLEGG HØGRE FOR M .20 NO 401
18		17378					1	STK	20	A40142011	GAFFELHODE FOR STREKKSKRUE M .20 NO 401
19		17379		A40142222	1	1	1	STK	10	A40142200	EMNE FOR GAFFEL M 22 NO 401
20		17379					1	STK	20	E_GJENGETAPP	(GJENGETAPP FOR 7/8" og 1" SJ
21		17379					1	STK	30	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
22		17380		A40142402	1	1	1	STK	10	A40142400	EMNE FOR GAFFEL M .24 NO 401/801
23		17380					1	STK	20	E_GJENGETAPP	GJENGETAPP FOR 7/8" og 1" SJ
24		17380					1	STK	30	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
25		17381		A00300902	1	1	1	STK	10	A3099002	PREGA EMNER FOR KRAGEPINN 10*38,5MM
26		17382		A40143222	1	1	1	STK	10	10 A40143200	EMNE FOR GAFFEL 1 1/4" NO 401/801
27		17382					1	STK	20	C_GJENGETAPP	GJENGETAPP FOR 1 1/4" SJ
28		17382					1	STK	30	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
29		17383		A40143822	1	1	1	STK	10	A40143800	EMNE FOR GAFFEL 1 1/2" NO 401/801
30		17383					1	STK	20	A_GJENGETAPP	(GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
31		17384		A00301102	1	1	1	STK	10	A1105011	IKKE BRUK BLANKTRUKKET STÅLST 37-2 11MM
32		17385		A40144502	1	1	1	STK	10	A40144500	EMNE FOR GAFFEL 13/4" NO 401/801
33		17385					1	STK	20	A_GJENGETAPP	GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
34		17386		A00301302	1	1	1	STK	10	A1105012	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 12N./M
35		17387		A40145222	1	1	1	STK	10	A40145200	EMNE FOR GAFFEL.2" NO 401/801
36		17387					1	STK	20	G_GJENGETAPP	GJENGETAPP FOR 2." og 2 1/2" SJ
37
38
39
40		17379	1	A40142200	EMNE FOR GAFFEL M 22 NO 401
41		17379	1	E_GJENGETAPP	(GJENGETAPP FOR 7/8" og 1" SJ
42		17379	1	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
43
Sheet1

Cell Formulas
Range	Formula
B40:E42	B40	=LET(styk,XLOOKUP($D$2,D5:D36,B5:B36,""),output,IFERROR(FILTER(B5:K36,B5:B36=styk),"no part"),CHOOSECOLS(output,1,6,9,10))
Dynamic array formulas.

 

[Jobb1Excel]

Or, if you wanted to just choose a few specific columns for your output, you may do it like this: To get desired output, again I type the Material Number I want to use into "D2"

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	########				Stykkliste
2				A40142222
3	Stykkliste
4		Stykkliste		Materialnummer	Alt	Bruk	Basiskvarn	BKE	Pos.	Komponent	Materialbetegnelse
5		17370		A00300601	1	1	1	STK	10	A1101008	IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM
6		17372		A40140602	1	1	1	STK	10	A1105006	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM
7		17372					1	STK	20	A1102205	IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM
8		17373		A40140802	1	1	1	STK	10	A1105008	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM
9		17373					1	STK	20	A1102202	IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM
10		17374		A40141002	1	1	1	STK	10	A40141009	SNITTA GAFFELLEGG HØGRE FOR M 10 NO 401
11		17374					1	STK	20	A40141011	GAFFELHODE FOR STREKKSKRUE M 10 NO 401
12		17375		A40141202	1	1	1	STK	10	A40141209	SNITTA GAFFELLEGG HØGRE FOR M 1.2 NO 401
13		17375					1	STK	20	A40141211	GAFFELHODE FOR STREKKSKRUE M 12 NO 401
14		17376		A00300802	1	1	1	STK	10	A3099000	IKKE BRUK EMNER FOR KRAGEPINN 8*3.2MM
15		17377		A40141602	1	1	1	STK	10	A40141609	SNITTA GAFFELLEGG HØGRE FOR M 16 NO 401
16		17377					1	STK	20	A40141611	GAFFELHODE FOR STREKKSKRUE M 16 NO 401
17		17378		A40142022	1	1	1	STK	10	A40142009	SNITTA GAFFELLEGG HØGRE FOR M .20 NO 401
18		17378					1	STK	20	A40142011	GAFFELHODE FOR STREKKSKRUE M .20 NO 401
19		17379		A40142222	1	1	1	STK	10	A40142200	EMNE FOR GAFFEL M 22 NO 401
20		17379					1	STK	20	E_GJENGETAPP	(GJENGETAPP FOR 7/8" og 1" SJ
21		17379					1	STK	30	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
22		17380		A40142402	1	1	1	STK	10	A40142400	EMNE FOR GAFFEL M .24 NO 401/801
23		17380					1	STK	20	E_GJENGETAPP	GJENGETAPP FOR 7/8" og 1" SJ
24		17380					1	STK	30	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
25		17381		A00300902	1	1	1	STK	10	A3099002	PREGA EMNER FOR KRAGEPINN 10*38,5MM
26		17382		A40143222	1	1	1	STK	10	10 A40143200	EMNE FOR GAFFEL 1 1/4" NO 401/801
27		17382					1	STK	20	C_GJENGETAPP	GJENGETAPP FOR 1 1/4" SJ
28		17382					1	STK	30	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
29		17383		A40143822	1	1	1	STK	10	A40143800	EMNE FOR GAFFEL 1 1/2" NO 401/801
30		17383					1	STK	20	A_GJENGETAPP	(GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
31		17384		A00301102	1	1	1	STK	10	A1105011	IKKE BRUK BLANKTRUKKET STÅLST 37-2 11MM
32		17385		A40144502	1	1	1	STK	10	A40144500	EMNE FOR GAFFEL 13/4" NO 401/801
33		17385					1	STK	20	A_GJENGETAPP	GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
34		17386		A00301302	1	1	1	STK	10	A1105012	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 12N./M
35		17387		A40145222	1	1	1	STK	10	A40145200	EMNE FOR GAFFEL.2" NO 401/801
36		17387					1	STK	20	G_GJENGETAPP	GJENGETAPP FOR 2." og 2 1/2" SJ
37
38
39
40		17379	1	A40142200	EMNE FOR GAFFEL M 22 NO 401
41		17379	1	E_GJENGETAPP	(GJENGETAPP FOR 7/8" og 1" SJ
42		17379	1	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
43
Sheet1

Cell Formulas
Range	Formula
B40:E42	B40	=LET(styk,XLOOKUP($D$2,D5:D36,B5:B36,""),output,IFERROR(FILTER(B5:K36,B5:B36=styk),"no part"),CHOOSECOLS(output,1,6,9,10))
Dynamic array formulas.

This is perfect! Thank you! Let's say i got a list of 30 diff material numbers on a diff sheet and want to lookup all at the same time. What would the formula look like? if A1 - A30 has different material numbers and i want to check the components of them all at the same time

 

[RobP]

Hi, thanks for the feedback. Somehow you need to get your 30 items into a place where a formula can interpret them and give you a result.
The only way for you to list the ones you want to look up would be to type them in somehow ... and if you're typing them in, then a formula can be adjusted to look at that relevant cell. But it would be quite tricky, as you have a differing number of components per material, so the rows between each formula is different.

Alternatively you would need to learn VBA coding, and get it to step through a list, but its not straightforward.

Hope it helps

Rob

 

[Jobb1Excel]

Hi, thanks for the feedback. Somehow you need to get your 30 items into a place where a formula can interpret them and give you a result.
The only way for you to list the ones you want to look up would be to type them in somehow ... and if you're typing them in, then a formula can be adjusted to look at that relevant cell. But it would be quite tricky, as you have a differing number of components per material, so the rows between each formula is different.

Alternatively you would need to learn VBA coding, and get it to step through a list, but its not straightforward.

Hope it helps

Rob

Okey, i will find a way to make that work Do you have any suggestions on how i can SUM all matching components? This is just so i can get the total needed number of each component based on the result of the material nr components lookup.

 

[Jobb1Excel]

The green text is the end product and quantity we are going to make. The numbers below the green text is the total qty needed of each compontent, so i need a formula to list up all components found and then total qty of the component next to it. example: On line 2 the component text is "EMNE FOR FORTØYNINGSBOLT Ø32 x 400MM" and total qty needed 60 so i want it that to show up in a seperate list by using a formula to get that information and lets say the same component is used for another product aswell and the qty is 60 then it would list it like this: "EMNE FOR FORTØYNINGSBOLT Ø32 x 400MM" in one cell and 120 in the cell right or left of the other cell. This is to get a total amount of each component needed to create the products we are going to make for the next week.

 

[RobP]

Re-looking at your original data and what you are trying to achieve, I went for a slightly different approach, starting again at the beginning, and taking all your comments into account. And this brought me to a solution over 3 Sheets. (Sheet1 = stykkliste, Sheet2 = Material Numbers + Qty to build, Sheet 3 = RESULT.

Firstly, I note the problem you have with your sykkliste is that your materialnummers are not all populated (eg. row7 and row9 are blank for example, so you will first need to ensure they are all filled. This is very easy, and I provided a formula in Column C to allow you to simply copy down in one go. Then you can cut & paste manually these values (not formulas) from Col C into Col D to complete the full list (I haven't done this copy myself just for clarity of instruction to you, so you will see that some of my formulas later refer to Col C when in fact you will need to refer to Col D.)

I have also edited the sykkliste a little on my example to show what happens when you get the "duplicate Komponent" situation later on Sheet2 & 3.

So Sheet1 looks like the list you provided : 1st step, complete those materialnummers.

byrow_filter.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	########				Stykkliste
2
3	Stykkliste
4		Stykkliste		Materialnummer	Alt	Bruk	Basiskvarn	BKE	Pos.	Komponent	Materialbetegnelse
5		17370	A00300601	A00300601	1	1	1	STK	10	A1101008	IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM
6		17372	A40140602	A40140602	1	1	2	STK	10	A1105006	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM
7		17372	A40140602				1	STK	20	A1102205	IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM
8		17373	A40140802	A40140802	1	1	1	STK	10	A1105008	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM
9		17373	A40140802				1	STK	20	A1102202	IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM
10		17374	A40141002	A40141002	1	1	1	STK	10	A40141009	SNITTA GAFFELLEGG HØGRE FOR M 10 NO 401
11		17374	A40141002				1	STK	20	A40141011	GAFFELHODE FOR STREKKSKRUE M 10 NO 401
12		17375	A40141202	A40141202	1	1	1	STK	10	A40141209	SNITTA GAFFELLEGG HØGRE FOR M 1.2 NO 401
13		17375	A40141202				1	STK	20	A40141211	GAFFELHODE FOR STREKKSKRUE M 12 NO 401
14		17376	A00300802	A00300802	1	1	1	STK	10	A3099000	IKKE BRUK EMNER FOR KRAGEPINN 8*3.2MM
15		17377	A40141602	A40141602	1	1	1	STK	10	A40141609	SNITTA GAFFELLEGG HØGRE FOR M 16 NO 401
16		17377	A40141602				1	STK	20	A40141611	GAFFELHODE FOR STREKKSKRUE M 16 NO 401
17		17378	A40142022	A40142022	1	1	1	STK	10	A40142009	SNITTA GAFFELLEGG HØGRE FOR M .20 NO 401
18		17378	A40142022				1	STK	20	A40142011	GAFFELHODE FOR STREKKSKRUE M .20 NO 401
19		17379	A40142222	A40142222	1	1	1	STK	10	A40142200	EMNE FOR GAFFEL M 22 NO 401
20		17379	A40142222				1	STK	20	E_GJENGETAPP	(GJENGETAPP FOR 7/8" og 1" SJ
21		17379	A40142222				1	STK	30	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
22		17380	A40142402	A40142402	1	1	1	STK	10	A40142400	EMNE FOR GAFFEL M .24 NO 401/801
23		17380	A40142402				1	STK	20	E_GJENGETAPP	GJENGETAPP FOR 7/8" og 1" SJ
24		17380	A40142402				1	STK	30	A_SKJÆREVERKTØY	SKJÆREVERKTØY KOSTNADER
25		17381	A00300902	A00300902	1	1	1	STK	10	A3099002	PREGA EMNER FOR KRAGEPINN 10*38,5MM
26		17382	A40143222	A40143222	1	1	1	STK	10	10 A40143200	EMNE FOR GAFFEL 1 1/4" NO 401/801
27		17382	A40143222				1	STK	20	C_GJENGETAPP	GJENGETAPP FOR 1 1/4" SJ
28		17382	A40143222				1	STK	30	A1102205	IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM
29		17383	A40143822	A40143822	1	1	1	STK	10	A40143800	EMNE FOR GAFFEL 1 1/2" NO 401/801
30		17383	A40143822				1	STK	20	A_GJENGETAPP	(GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
31		17384	A00301102	A00301102	1	1	1	STK	10	A1105011	IKKE BRUK BLANKTRUKKET STÅLST 37-2 11MM
32		17385	A40144502	A40144502	1	1	1	STK	10	A40144500	EMNE FOR GAFFEL 13/4" NO 401/801
33		17385	A40144502				1	STK	20	A_GJENGETAPP	GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
34		17386	A00301302	A00301302	1	1	1	STK	10	A1105012	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 12N./M
35		17387	A40145222	A40145222	1	1	1	STK	10	A40145200	EMNE FOR GAFFEL.2" NO 401/801
36		17387	A40145222				1	STK	20	G_GJENGETAPP	GJENGETAPP FOR 2." og 2 1/2" SJ
Sheet1

Cell Formulas
Range	Formula
C5:C36	C5	=IF(D5<>"",D5,C4)

Then on Sheet2, you have your list of material nummers you wish to build (Col A), and the QTY of each in col B. I add a formula in D2 (you might need to refer it to Col D not col C as mentioned earlier) to extract the list of parts needed. I added a formula in Col N to pull in the required amount for each build, and then another formula in Col O which will give you the Qty * total. So, for example N2 multiplies Qty 2 (from I3) * 150 (from A3) to get your 300 total.

The rows here can have duplicate Komponents in the list. (eg Row 4 and Row 10. are duplicate, so I added another formula in Col O to give you the totals of all the duplicates (150 + 40 now = 190) and this 190 is therefore duplicated in Row 4 and Row 10 now.

byrow_filter.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	BUILD	QTY							qty					total	sum totals
2	A00300601	60		17370	A00300601	A00300601	1	1	1	STK	10	A1101008	IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM	60	60
3	A40140602	150		17372	A40140602	A40140602	1	1	2	STK	10	A1105006	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM	300	300
4	A40140802	400		17372	A40140602	0	0	0	1	STK	20	A1102205	IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM	150	190
5	A00300902	100		17373	A40140802	A40140802	1	1	1	STK	10	A1105008	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM	400	400
6	A40143222	40		17373	A40140802	0	0	0	1	STK	20	A1102202	IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM	400	400
7				17381	A00300902	A00300902	1	1	1	STK	10	A3099002	PREGA EMNER FOR KRAGEPINN 10*38,5MM	100	100
8				17382	A40143222	A40143222	1	1	1	STK	10	10 A40143200	EMNE FOR GAFFEL 1 1/4" NO 401/801	40	40
9				17382	A40143222	0	0	0	1	STK	20	C_GJENGETAPP	GJENGETAPP FOR 1 1/4" SJ	40	40
10				17382	A40143222	0	0	0	1	STK	30	A1102205	IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM	40	190
11
Sheet2

Cell Formulas
Range	Formula
D2:M10	D2	=FILTER(Sheet1!B5:K36,BYROW(Sheet1!C5:C36,LAMBDA(r,COUNTIF(A2:A6,r))))
N2:N10	N2	=I2*XLOOKUP(E2,A$2:A$6,B$2:B$6)
O2:O10	O2	=SUMIF(L$2:L$10,L2,N$2:N$10)
Dynamic array formulas.

so the last step in Sheet 3 is to get you just the columns you wanted, and of course to remove the duplicate rows

byrow_filter.xlsx
	A	B	C
1	A1101008	IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM	60
2	A1105006	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM	300
3	A1102205	IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM	190
4	A1105008	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM	400
5	A1102202	IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM	400
6	A3099002	PREGA EMNER FOR KRAGEPINN 10*38,5MM	100
7	10 A40143200	EMNE FOR GAFFEL 1 1/4" NO 401/801	40
8	C_GJENGETAPP	GJENGETAPP FOR 1 1/4" SJ	40
Sheet3

Cell Formulas
Range	Formula
A1:C8	A1	=UNIQUE(FILTER(Sheet2!L2:O10,{1,1,0,1}))
Dynamic array formulas.

You will need to adjust the manual formulas in sheet 2 (I mean ensure they are copied down to all the rows of output you have (depending on how long your material list is), but should be quite easy each time you execute.

Hope thats okay for you

cheers
Rob

 

[Jobb1Excel]

Thank you! One last thing im looking for is to search for the material name of the product in a sales sheet and then to display all order QTY's and dates of each order in different cells.

in this example we will be looking up A085228 and A085522 (The formula will be used to each product) and i want it the output to be QTY in row "K" and date in row "I". In this example both of the material numbers has 2 matches in the list as you can see below, but it should list all matches no matter how many different matches it is. I made them green in the example aswell to make it easy to see them. The numbers in row "K" is total amount purchased by customers, so i want the results below that number of each product, so on the same line as the components. So for the A085228 the output would have been "200" in one cell and "30.11.2023" in the cell next to it and then on the line below "600" in one cell and "08.12.2023" in the next cell.

A085522: "100" in the cell below 1414 and "08.12.2023" in the cell next to it and on the lines below "200" in the cell below "100" and "06.12.2023" in the cell below "08.12.2023"

The total QTY's is showing wrong numbers beacause there is matches below in the list that is not shown in this example.

 

[RobP]

Honestly, my strong advice to you would be to stop using Excel for such application, and switch to MS Access (a database application), as this is much more suitable for the kind of work you are wanting to do.

The problem with formulas in Excel is that they are live. Any change on one sheet will affect what another sheet could see without you realising it. For example you wanted to look at materials to be built next week, and we gave you an output for that. But what happens when you want to do the same for the week after ? Well, I can tell you - the data gets overwritten and you lost last weeks. Does that affect your purchase orders ? how will you total or remember what you used on last week ? It becomes very messy very quickly.

As far as I know, you cannot do an XLOOKUP for several items in a list, and add another field in that list ie. Qty together to give you a total number of orders.

In my opinion you would be better off just filtering your purchase order list for the part numbers you want to see, and having a subtotal at the bottom. SUBTOTAL, or AGGREGATE are commands you need to look at for that.

cheers
Rob