Need help with a formula

J

Jobb1Excel

New Member
Joined
Oct 17, 2023
Messages
22
Office Version
  1. 365
Platform
  1. Windows
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:

1701686078998.png


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.
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
Hi, does this give you what you need ?

Book1
ABCDEFGHIJK
1########Stykkliste
2A40140802
3Stykkliste
4StykklisteMaterialnummerAltBrukBasiskvarnBKEPos.KomponentMaterialbetegnelse
517370A00300601111STK10A1101008IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM
617372A40140602111STK10A1105006IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM
7173721STK20A1102205IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM
817373A40140802111STK10A1105008IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM
9173731STK20A1102202IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM
1017374A40141002111STK10A40141009SNITTA GAFFELLEGG HØGRE FOR M 10 NO 401
11173741STK20A40141011GAFFELHODE FOR STREKKSKRUE M 10 NO 401
1217375A40141202111STK10A40141209SNITTA GAFFELLEGG HØGRE FOR M 1.2 NO 401
13173751STK20A40141211GAFFELHODE FOR STREKKSKRUE M 12 NO 401
1417376A00300802111STK10A3099000IKKE BRUK EMNER FOR KRAGEPINN 8*3.2MM
1517377A40141602111STK10A40141609SNITTA GAFFELLEGG HØGRE FOR M 16 NO 401
16173771STK20A40141611GAFFELHODE FOR STREKKSKRUE M 16 NO 401
1717378A40142022111STK10A40142009SNITTA GAFFELLEGG HØGRE FOR M .20 NO 401
18173781STK20A40142011GAFFELHODE FOR STREKKSKRUE M .20 NO 401
1917379A40142222111STK10A40142200EMNE FOR GAFFEL M 22 NO 401
20173791STK20E_GJENGETAPP(GJENGETAPP FOR 7/8" og 1" SJ
21173791STK30A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
2217380A40142402111STK10A40142400EMNE FOR GAFFEL M .24 NO 401/801
23173801STK20E_GJENGETAPPGJENGETAPP FOR 7/8" og 1" SJ
24173801STK30A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
2517381A00300902111STK10A3099002PREGA EMNER FOR KRAGEPINN 10*38,5MM
2617382A40143222111STK1010 A40143200EMNE FOR GAFFEL 1 1/4" NO 401/801
27173821STK20C_GJENGETAPPGJENGETAPP FOR 1 1/4" SJ
28173821STK30A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
2917383A40143822111STK10A40143800EMNE FOR GAFFEL 1 1/2" NO 401/801
30173831STK20A_GJENGETAPP(GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
3117384A00301102111STK10A1105011IKKE BRUK BLANKTRUKKET STÅLST 37-2 11MM
3217385A40144502111STK10A40144500EMNE FOR GAFFEL 13/4" NO 401/801
33173851STK20A_GJENGETAPPGJENGETAPP FOR 1 1/2" og 1 3/4" SJ
3417386A00301302111STK10A1105012IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 12N./M
3517387A40145222111STK10A40145200EMNE FOR GAFFEL.2" NO 401/801
36173871STK20G_GJENGETAPPGJENGETAPP FOR 2." og 2 1/2" SJ
37
38
39
40173730A40140802111STK10A1105008IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM
411737300001STK20A1102202IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM
Sheet1
Cell Formulas
RangeFormula
B40:K41B40=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.
 
Upvote 0
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
ABCDEFGHIJK
1########Stykkliste
2A40142222
3Stykkliste
4StykklisteMaterialnummerAltBrukBasiskvarnBKEPos.KomponentMaterialbetegnelse
517370A00300601111STK10A1101008IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM
617372A40140602111STK10A1105006IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM
7173721STK20A1102205IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM
817373A40140802111STK10A1105008IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM
9173731STK20A1102202IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM
1017374A40141002111STK10A40141009SNITTA GAFFELLEGG HØGRE FOR M 10 NO 401
11173741STK20A40141011GAFFELHODE FOR STREKKSKRUE M 10 NO 401
1217375A40141202111STK10A40141209SNITTA GAFFELLEGG HØGRE FOR M 1.2 NO 401
13173751STK20A40141211GAFFELHODE FOR STREKKSKRUE M 12 NO 401
1417376A00300802111STK10A3099000IKKE BRUK EMNER FOR KRAGEPINN 8*3.2MM
1517377A40141602111STK10A40141609SNITTA GAFFELLEGG HØGRE FOR M 16 NO 401
16173771STK20A40141611GAFFELHODE FOR STREKKSKRUE M 16 NO 401
1717378A40142022111STK10A40142009SNITTA GAFFELLEGG HØGRE FOR M .20 NO 401
18173781STK20A40142011GAFFELHODE FOR STREKKSKRUE M .20 NO 401
1917379A40142222111STK10A40142200EMNE FOR GAFFEL M 22 NO 401
20173791STK20E_GJENGETAPP(GJENGETAPP FOR 7/8" og 1" SJ
21173791STK30A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
2217380A40142402111STK10A40142400EMNE FOR GAFFEL M .24 NO 401/801
23173801STK20E_GJENGETAPPGJENGETAPP FOR 7/8" og 1" SJ
24173801STK30A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
2517381A00300902111STK10A3099002PREGA EMNER FOR KRAGEPINN 10*38,5MM
2617382A40143222111STK1010 A40143200EMNE FOR GAFFEL 1 1/4" NO 401/801
27173821STK20C_GJENGETAPPGJENGETAPP FOR 1 1/4" SJ
28173821STK30A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
2917383A40143822111STK10A40143800EMNE FOR GAFFEL 1 1/2" NO 401/801
30173831STK20A_GJENGETAPP(GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
3117384A00301102111STK10A1105011IKKE BRUK BLANKTRUKKET STÅLST 37-2 11MM
3217385A40144502111STK10A40144500EMNE FOR GAFFEL 13/4" NO 401/801
33173851STK20A_GJENGETAPPGJENGETAPP FOR 1 1/2" og 1 3/4" SJ
3417386A00301302111STK10A1105012IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 12N./M
3517387A40145222111STK10A40145200EMNE FOR GAFFEL.2" NO 401/801
36173871STK20G_GJENGETAPPGJENGETAPP FOR 2." og 2 1/2" SJ
37
38
39
40173791A40142200EMNE FOR GAFFEL M 22 NO 401
41173791E_GJENGETAPP(GJENGETAPP FOR 7/8" og 1" SJ
42173791A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
43
Sheet1
Cell Formulas
RangeFormula
B40:E42B40=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.
 
Upvote 0
RobP said:
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
ABCDEFGHIJK
1########Stykkliste
2A40142222
3Stykkliste
4StykklisteMaterialnummerAltBrukBasiskvarnBKEPos.KomponentMaterialbetegnelse
517370A00300601111STK10A1101008IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM
617372A40140602111STK10A1105006IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM
7173721STK20A1102205IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM
817373A40140802111STK10A1105008IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM
9173731STK20A1102202IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM
1017374A40141002111STK10A40141009SNITTA GAFFELLEGG HØGRE FOR M 10 NO 401
11173741STK20A40141011GAFFELHODE FOR STREKKSKRUE M 10 NO 401
1217375A40141202111STK10A40141209SNITTA GAFFELLEGG HØGRE FOR M 1.2 NO 401
13173751STK20A40141211GAFFELHODE FOR STREKKSKRUE M 12 NO 401
1417376A00300802111STK10A3099000IKKE BRUK EMNER FOR KRAGEPINN 8*3.2MM
1517377A40141602111STK10A40141609SNITTA GAFFELLEGG HØGRE FOR M 16 NO 401
16173771STK20A40141611GAFFELHODE FOR STREKKSKRUE M 16 NO 401
1717378A40142022111STK10A40142009SNITTA GAFFELLEGG HØGRE FOR M .20 NO 401
18173781STK20A40142011GAFFELHODE FOR STREKKSKRUE M .20 NO 401
1917379A40142222111STK10A40142200EMNE FOR GAFFEL M 22 NO 401
20173791STK20E_GJENGETAPP(GJENGETAPP FOR 7/8" og 1" SJ
21173791STK30A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
2217380A40142402111STK10A40142400EMNE FOR GAFFEL M .24 NO 401/801
23173801STK20E_GJENGETAPPGJENGETAPP FOR 7/8" og 1" SJ
24173801STK30A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
2517381A00300902111STK10A3099002PREGA EMNER FOR KRAGEPINN 10*38,5MM
2617382A40143222111STK1010 A40143200EMNE FOR GAFFEL 1 1/4" NO 401/801
27173821STK20C_GJENGETAPPGJENGETAPP FOR 1 1/4" SJ
28173821STK30A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
2917383A40143822111STK10A40143800EMNE FOR GAFFEL 1 1/2" NO 401/801
30173831STK20A_GJENGETAPP(GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
3117384A00301102111STK10A1105011IKKE BRUK BLANKTRUKKET STÅLST 37-2 11MM
3217385A40144502111STK10A40144500EMNE FOR GAFFEL 13/4" NO 401/801
33173851STK20A_GJENGETAPPGJENGETAPP FOR 1 1/2" og 1 3/4" SJ
3417386A00301302111STK10A1105012IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 12N./M
3517387A40145222111STK10A40145200EMNE FOR GAFFEL.2" NO 401/801
36173871STK20G_GJENGETAPPGJENGETAPP FOR 2." og 2 1/2" SJ
37
38
39
40173791A40142200EMNE FOR GAFFEL M 22 NO 401
41173791E_GJENGETAPP(GJENGETAPP FOR 7/8" og 1" SJ
42173791A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
43
Sheet1
Cell Formulas
RangeFormula
B40:E42B40=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.
Click to expand...
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
 
Upvote 0
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
 
Upvote 0
RobP said:
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
Click to expand...
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.
 
Upvote 0
1701777279989.png


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.
 
Upvote 0
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
ABCDEFGHIJKL
1########Stykkliste
2
3Stykkliste
4StykklisteMaterialnummerAltBrukBasiskvarnBKEPos.KomponentMaterialbetegnelse
517370A00300601A00300601111STK10A1101008IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM
617372A40140602A40140602112STK10A1105006IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM
717372A401406021STK20A1102205IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM
817373A40140802A40140802111STK10A1105008IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM
917373A401408021STK20A1102202IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM
1017374A40141002A40141002111STK10A40141009SNITTA GAFFELLEGG HØGRE FOR M 10 NO 401
1117374A401410021STK20A40141011GAFFELHODE FOR STREKKSKRUE M 10 NO 401
1217375A40141202A40141202111STK10A40141209SNITTA GAFFELLEGG HØGRE FOR M 1.2 NO 401
1317375A401412021STK20A40141211GAFFELHODE FOR STREKKSKRUE M 12 NO 401
1417376A00300802A00300802111STK10A3099000IKKE BRUK EMNER FOR KRAGEPINN 8*3.2MM
1517377A40141602A40141602111STK10A40141609SNITTA GAFFELLEGG HØGRE FOR M 16 NO 401
1617377A401416021STK20A40141611GAFFELHODE FOR STREKKSKRUE M 16 NO 401
1717378A40142022A40142022111STK10A40142009SNITTA GAFFELLEGG HØGRE FOR M .20 NO 401
1817378A401420221STK20A40142011GAFFELHODE FOR STREKKSKRUE M .20 NO 401
1917379A40142222A40142222111STK10A40142200EMNE FOR GAFFEL M 22 NO 401
2017379A401422221STK20E_GJENGETAPP(GJENGETAPP FOR 7/8" og 1" SJ
2117379A401422221STK30A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
2217380A40142402A40142402111STK10A40142400EMNE FOR GAFFEL M .24 NO 401/801
2317380A401424021STK20E_GJENGETAPPGJENGETAPP FOR 7/8" og 1" SJ
2417380A401424021STK30A_SKJÆREVERKTØYSKJÆREVERKTØY KOSTNADER
2517381A00300902A00300902111STK10A3099002PREGA EMNER FOR KRAGEPINN 10*38,5MM
2617382A40143222A40143222111STK1010 A40143200EMNE FOR GAFFEL 1 1/4" NO 401/801
2717382A401432221STK20C_GJENGETAPPGJENGETAPP FOR 1 1/4" SJ
2817382A401432221STK30A1102205IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM
2917383A40143822A40143822111STK10A40143800EMNE FOR GAFFEL 1 1/2" NO 401/801
3017383A401438221STK20A_GJENGETAPP(GJENGETAPP FOR 1 1/2" og 1 3/4" SJ
3117384A00301102A00301102111STK10A1105011IKKE BRUK BLANKTRUKKET STÅLST 37-2 11MM
3217385A40144502A40144502111STK10A40144500EMNE FOR GAFFEL 13/4" NO 401/801
3317385A401445021STK20A_GJENGETAPPGJENGETAPP FOR 1 1/2" og 1 3/4" SJ
3417386A00301302A00301302111STK10A1105012IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 12N./M
3517387A40145222A40145222111STK10A40145200EMNE FOR GAFFEL.2" NO 401/801
3617387A401452221STK20G_GJENGETAPPGJENGETAPP FOR 2." og 2 1/2" SJ
Sheet1
Cell Formulas
RangeFormula
C5:C36C5=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
ABCDEFGHIJKLMNOP
1BUILDQTYqtytotalsum totals
2A003006016017370A00300601A00300601111STK10A1101008IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM6060
3A4014060215017372A40140602A40140602112STK10A1105006IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM300300
4A4014080240017372A401406020001STK20A1102205IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM150190
5A0030090210017373A40140802A40140802111STK10A1105008IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM400400
6A401432224017373A401408020001STK20A1102202IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM400400
717381A00300902A00300902111STK10A3099002PREGA EMNER FOR KRAGEPINN 10*38,5MM100100
817382A40143222A40143222111STK1010 A40143200EMNE FOR GAFFEL 1 1/4" NO 401/8014040
917382A401432220001STK20C_GJENGETAPPGJENGETAPP FOR 1 1/4" SJ4040
1017382A401432220001STK30A1102205IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM40190
11
Sheet2
Cell Formulas
RangeFormula
D2:M10D2=FILTER(Sheet1!B5:K36,BYROW(Sheet1!C5:C36,LAMBDA(r,COUNTIF(A2:A6,r))))
N2:N10N2=I2*XLOOKUP(E2,A$2:A$6,B$2:B$6)
O2:O10O2=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
ABC
1A1101008IKKE BRUK VARMVALSA RUNDSTÅLST 378 MM60
2A1105006IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 6MM300
3A1102205IKKE BRUK BLANKVALSA FLATT ST.37-2 45*3MM190
4A1105008IKKE BRUK BLANKTRUKKET STÅLST 3.7-2 8MM400
5A1102202IKKE BRUK BLANKVALSA FLATT ST.37-2 50*4MM400
6A3099002PREGA EMNER FOR KRAGEPINN 10*38,5MM100
710 A40143200EMNE FOR GAFFEL 1 1/4" NO 401/80140
8C_GJENGETAPPGJENGETAPP FOR 1 1/4" SJ40
Sheet3
Cell Formulas
RangeFormula
A1:C8A1=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
 
Upvote 0
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.

1702016717016.png


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.


1702017120466.png
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Need help with formula
Replies
3
Views
194
RobP
R
B
  • Question Question
Need formula help
2
Replies
10
Views
867
NateSC
NateSC
J
  • Solved
sumif or sumifs formula which does not sum if one or more of the values is zero.
2
Replies
12
Views
1K
Eric W
Eric W
Z
  • Question Question
Help with Formulas!
Replies
1
Views
88
zarealshook
Z
J
  • Question Question
Copying and splitting data between sheets
Replies
3
Views
349
Jumparound
J

Forum statistics

Threads
1,224,823
Messages
6,181,176
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top