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.

 

[Jobb1Excel]

Im trying to keep it as simple as possible, so all i basically need is a formula to check for matr nr vlookup the first match qty and date from that match and then in the cell under a formula to do the same for 2nd match and then i just drag it down and manually filter empty cells etc.

Example:
A2 <- matr nr.
If A2 has a match in sales sheet then return qty and date of the first match
cell under
If A2 has a second match then return qty and date of the second match
cell under
If A2 has a third match then return qty and date of the third match

 

[RobP]

ok, well I can only offer my advice ... you don't need to use it of course.

You have the advantage of seeing all your sheets and knowing what is where, whereas I here just have a picture of a screen grab. So its a bit tricky to follow - please take a look at the XL2BB tool we have on here to post your data, as it means more chance of people finding time to help (eg, rather than like me trying to type manually some of your data from a picture). You'll get better results from the forum in this way.

So as far as I can tell (again, you have the advantage of Swedish .. whereas to me this data all looks like a foreign language without much meaning (sorry)), I think this will get what you are asking.
The formula of interest to you is in K4.

byrow_filter.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1											Order	Date
2						A1101008	IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM	60			200	30.11.2023
3						A1105006	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM	300			600	08.12.2023
4						A1102205	IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM	190
5						A1105008	IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM	400
6						A1102202	IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM	400
7						A3099002	PREGA EMNER FOR KRAGEPINN 10*38,5MM	100
8						10 A40143200	EMNE FOR GAFFEL 1 1/4" NO 401/801	40
9						C_GJENGETAPP	GJENGETAPP FOR 1 1/4" SJ	40
10
11
12
Sheet3

Cell Formulas
Range	Formula
F2:H9	F2	=UNIQUE(FILTER(Sheet2!L2:O10,{1,1,0,1}))
K2:L3	K2	=CHOOSECOLS(FILTER(Sheet4!A2:I10,Sheet4!F2:F10=F2),8,9)
Dynamic array formulas.

My Sheet4 is set up as such (to try and reflect your image partially), so you can see how the formula is operating.

byrow_filter.xlsx
	A	B	C	D	E	F	G	H	I	J
1						MATR.NR		Qty	Deliv Date
2	30.11.2023	B005911	1622995 20.2451 customer 1			A1101008		200	30.11.2023
3	04.12.2023	B005912	16.22995 .2.22451 customer 2			A1105008		100	08.12.2023
4	29.11.2023	B005913	162.2995 .2.22451ocustomer 3			2789094		122	30.11.2023
5	30.11.2023	B005914	16.22995 .2.22451 customer 4			2021181		1	01.12.2023
6	29.11.2023	B005915	16.22995 202451 customer5			1035018		4	01.12.2023
7	27.11.2023	B005916	16.22995 .2.22451 customer 6			1017001		3	01.12.2023
8	04.12.2023	B005917	162.2995 .2.22451 customer 7			A1101008		600	08.12.2023
9	24.11.2023	B005918	16.22995 .2.22451 customer 8			2701669		4	01.12.2023
10	04.12.2023	B005919	16.22995 202451 customer 9			A0730125		20	04.12.2023
11	04.12.2023	B005920	16.22995 .2.22451 customer 10			A999999		2	04.12.2023
12	27.11.2023	B005921	162.2995 .2.22451 customer 11			2789040		8	05.12.2023
13	30.11.2023	B005922	16.22995 .2.22451 customer 12			A1105008		200	06.12.2023
Sheet4

Good luck.

cheers
ROb

 

[RobP]

sorry, I mean K2..

 

[Jobb1Excel]

Thank you so much for the help! Got exactly what i was looking for.