Need formula help

[BluRid]

Hey, i got a excel sheet with product name, components needed to create the product and the component storage stock. It looks like this:

Each component needed for the product is listed below each product (i got 10 lines with a formula to get the components of each product and i just filter the blank cells because there is no one specific number of components. Most got 3-8 components and no products has over 10 components).
The number next to the product name is the qty of that product we will be making and the number needed of each component to create the product next to each component and then component stock next to the number of needed components.

So what im looking for is 2 things: in D2 and D12 i want formulas that checks if all component numbers in A2 and A12 is higher then the component stock number easily check if the product component status is "OK" or "MISSING COMPONENTS". So one formula checks A2 and one checks A12.

Im also looking for a formula to display all components that has a lower component needed number then component stock so i easily can get a overview of the components missing from all the products. It should be a list that display all component names and the amount that we are missing.

example of the second thing im looking for:
lets say component6 and component7 numbers was higher then component stock number on both of them
component6 stock 200
component7 stock 200


i want a formula that would display component6 in one cell and in the next cell 300 - 200 (stock number - number needed)
output: "component6" in one cell and "100 needed" in the next cell and then in the cells under "component7" and "100 needed" in the next cell.

 

[NateSC]

You should be able to do everything with IF statements. Let me know if you were looking for something different.

temp macro work.xlsm
	A	B	C	D	E	F
1			Component stock		Component Needed	Qty Needed
2	Product Name	150		OK
3	Component1	150	300
4	Component2	150	150
5	Component3	300	300
6
7
8
9
10
11
12	Product Name	300		MISSING COMPONENTS
13	Component1	300	300
14	Component2	300	1000
15	Component3	600	1000
16	Component4	600	1500
17	Component5	300	300
18	Component6	300	200		Component6	100
19	Component7	300	20		Component7	280
20	Component8	300	323
Sheet17

Cell Formulas
Range	Formula
D2,D12	D2	=IF(AND(C3>=B3,C4>=B4,C5>=B5,C6>=B6,C7>=B7,C8>=B8,C9>=B9,C10>=B10,C11>=B11),"OK","MISSING COMPONENTS")
E3:E11,E13:E20	E3	=IF(F3="","",A3)
F3:F11,F13:F20	F3	=IF(C3<B3,B3-C3,"")

 

[BluRid]

Hey NateSC, I forgot to mention that the numbers in "component stock" (Row C) has "STK" behind the number
examples: C13 = 300 STK
C14 = 1000 STK
C15 = 1000 STK
C16 = 1500 STK
i tested the D2 formula and it returns all as OK probably because of the "STK"

 

[NateSC]

That will make a difference. Would it be an option to create another column where there is an equation to pull off the quantity without "STK"? This would just make the equations a bit cleaner. It can all be done in one step, but the equation is going to be REALLY long.

 

[NateSC]

Well, here it is in all of its ungainliness. The alternate is to add a column as mentioned above and then change the original equations to look at that column. Its just an alternate. It should provide the same result either way.

temp macro work.xlsm
	A	B	C	D	E	F
1			Component stock		Component Needed	Qty Needed
2	Product Name	150		OK
3	Component1	150	150 STK
4	Component2	150	150 STK
5	Component3	300	300 STK
6
7
8
9
10
11
12	Product Name	300		MISSING COMPONENTS
13	Component1	300	300 STK
14	Component2	300	1000 STK
15	Component3	600	1000 STK
16	Component4	600	1500 STK
17	Component5	300	300 STK
18	Component6	300	200 STK		Component6	100
19	Component7	300	20 STK		Component7	280
20	Component8	300	323 STK
21
Sheet17

Cell Formulas
Range	Formula
D2,D12	D2	=IF(AND(NUMBERVALUE(IFERROR(LEFT(C3,SEARCH(" ",C3)-1),""))>=B3,NUMBERVALUE(IFERROR(LEFT(C4,SEARCH(" ",C4)-1),""))>=B4,NUMBERVALUE(IFERROR(LEFT(C5,SEARCH(" ",C5)-1),""))>=B5,NUMBERVALUE(IFERROR(LEFT(C6,SEARCH(" ",C6)-1),""))>=B6,NUMBERVALUE(IFERROR(LEFT(C7,SEARCH(" ",C7)-1),""))>=B7,NUMBERVALUE(IFERROR(LEFT(C8,SEARCH(" ",C8)-1),""))>=B8,NUMBERVALUE(IFERROR(LEFT(C9,SEARCH(" ",C9)-1),""))>=B9,NUMBERVALUE(IFERROR(LEFT(C10,SEARCH(" ",C10)-1),""))>=B10,NUMBERVALUE(IFERROR(LEFT(C11,SEARCH(" ",C11)-1),""))>=B11),"OK","MISSING COMPONENTS")
E3:E11,E13:E21	E3	=IF(F3="","",A3)
F3:F11,F13:F21	F3	=IF(NUMBERVALUE(IFERROR(LEFT(C3,SEARCH(" ",C3)-1),""))<B3,B3-NUMBERVALUE(IFERROR(LEFT(C3,SEARCH(" ",C3)-1),"")),"")

 

[NateSC]

Is this what you needed? If you add the extra column, you can do the "NUBERVALUE(IFERROR..." part in that column to get the number separated from the STK. Then the equations would basically be the same as the first solution (though you might need to change a column reference for the value depending on where you split it out).

 

[BluRid]

Is this what you needed? If you add the extra column, you can do the "NUBERVALUE(IFERROR..." part in that column to get the number separated from the STK. Then the equations would basically be the same as the first solution (though you might need to change a column reference for the value depending on where you split it out).

Hey, i might be doing something wrong, but im getting error #NAME when trying all those 3 formulas. Sorry for the late respond btw.

 

[NateSC]

Does Column C look like:
A) 200 STK - with a space between the number and STK
B) 200STK - without a space between the number and STK
C) something else?

 

[NateSC]

Try the following and see if it works to pull the value from the Stock column for row 3:

=NUMBERVALUE(IFERROR(LEFT(C3,SEARCH(" ",C3)-1),""))

 

[BluRid]

Try the following and see if it works to pull the value from the Stock column for row 3:

=NUMBERVALUE(IFERROR(LEFT(C3,SEARCH(" ",C3)-1),""))

That formula works and the Column C looks like "A)"