Good day guys,
I have a difficult formula that I need for a project. Not sure what to name the formula or the project.
I have order qty's in Column B that has to order in the order qtys' provided by a supplier. But the order qty's differ from supplier to supplier.
For now the variables. C01 qty profile is in Column R, SP01 qty profile is in Column Q and SHP0 qty profile is in Column P. but if the Profile is SP01 it has to look for the profile qty in Column P first. If there is a qty that should be used. If Column P is = to zero the qty in Column Q is needed.
Row 106 as example. The Profile needed is C01. The order qty is 2, which is less than what the suppliers needs. We need to order min of 12 or multiple of 12's so this is less than 12 so the Order Check should be 12. If the order qty was 15 it should order in multiples of 12 so in other words the order check must be 24.
Row 157 as Example. SHP0 is the required profile. The profile qty is 2, the order is 2 so the Order Check should remain 2. If the order Qty was 3, the order check should have been 4.
If Column P,Q,R is "0" or blank the order check should be the same as the order qty. There are other Order Profile such as SPH08 etc. they can be ignored, same value as in Column B should be in Column C.
Here is another set of data that is in the same table as above with different Order Profile
The order Check (C) should be equal to the Value in column Z if the order value is less than in Column Z, but if the order is more than the Value in Column Z, we have to order in multiples of it.
Row 268 as example. Order qty "B" is 1, the profile indicates no less than 10 units "z" so we should order 10. If "B" was 15 we should order 20
Row 284 as example. Order qty is 3064, the profile indicates units of 1000. So we have to order 4000. in this instance I would round it to 3000 as 64 units nothing, but consider row 285 I would round it up to 3000.
Is there any formula we can use to assist us with this. Its more than 30 000 rows of data, to do it 1 by 1 will take us an eternity. Thank you in advance.
I have a difficult formula that I need for a project. Not sure what to name the formula or the project.
I have order qty's in Column B that has to order in the order qtys' provided by a supplier. But the order qty's differ from supplier to supplier.
For now the variables. C01 qty profile is in Column R, SP01 qty profile is in Column Q and SHP0 qty profile is in Column P. but if the Profile is SP01 it has to look for the profile qty in Column P first. If there is a qty that should be used. If Column P is = to zero the qty in Column Q is needed.
Row 106 as example. The Profile needed is C01. The order qty is 2, which is less than what the suppliers needs. We need to order min of 12 or multiple of 12's so this is less than 12 so the Order Check should be 12. If the order qty was 15 it should order in multiples of 12 so in other words the order check must be 24.
Row 157 as Example. SHP0 is the required profile. The profile qty is 2, the order is 2 so the Order Check should remain 2. If the order Qty was 3, the order check should have been 4.
If Column P,Q,R is "0" or blank the order check should be the same as the order qty. There are other Order Profile such as SPH08 etc. they can be ignored, same value as in Column B should be in Column C.
Here is another set of data that is in the same table as above with different Order Profile
The order Check (C) should be equal to the Value in column Z if the order value is less than in Column Z, but if the order is more than the Value in Column Z, we have to order in multiples of it.
Row 268 as example. Order qty "B" is 1, the profile indicates no less than 10 units "z" so we should order 10. If "B" was 15 we should order 20
Row 284 as example. Order qty is 3064, the profile indicates units of 1000. So we have to order 4000. in this instance I would round it to 3000 as 64 units nothing, but consider row 285 I would round it up to 3000.
Is there any formula we can use to assist us with this. Its more than 30 000 rows of data, to do it 1 by 1 will take us an eternity. Thank you in advance.
