Forum Discussion
Formula/VBA Help
Riny_van_Eekelen Sir, If you can help me with the below issue, which is similar to "https://techcommunity.microsoft.com/t5/excel/formula-vba-help/m-p/2979488#M122763" I would be greatful to you.
My problem.
In column A, I've a concatenate of my clothes catogories, and
In column B, I've the Cartons which contains those clothes
In column C, I have qty per Carton
In Column D, is my requirement for entire Categories of clothes
In Column E, Sumif of total qty of clothes categories in "column A"
I need, that if a categories in Column A, has 4 or "n" number of boxes with 10 or "n" number of quantity, then in column F, I can get the cartons which can fulfil my quantity and remaining carton will be zero.
For Example, A Categories has 4 cartons with 10 each so the sum is 40 qty. My requirement is 30, so I want only 3 box can be fetched and one will remain there.
I hope I was able to convey my problem statement
sherikhan88 Glad it worked. The part of the formula you refer to is just to "reset the calculation in column F when the product code in column A changes AND there's nothing more to be fulfilled. The N() function allows you to reference text fields in calculations. Entering E2-E1 would result in an error as E1 contains a text, i.e. the column header. Wrapping E1 in N( ) returns zero in this case. Alternatively, you could use SUM(E2,-E1) as sum ignores texts. whatever you prefer.
To understand what --(A2=A1) does, just enter it in an empty cell on row 2, with an= sign in front, of course, and drag it down.
5 Replies
sherikhan88 Perhaps like in the attached file. Not sure about how you got to the answers that I marked in yellow.
- Sir, you are became my ideal. Amazing, wonderful, excellent. Thank you so much for your help, that is exactly how I want to see the numbers.
If you dont mind could you please enlighten me that what is below mentioned function is doing in fulfilment formula.
E2-N(E1)*--(A2=A1)sherikhan88 Glad it worked. The part of the formula you refer to is just to "reset the calculation in column F when the product code in column A changes AND there's nothing more to be fulfilled. The N() function allows you to reference text fields in calculations. Entering E2-E1 would result in an error as E1 contains a text, i.e. the column header. Wrapping E1 in N( ) returns zero in this case. Alternatively, you could use SUM(E2,-E1) as sum ignores texts. whatever you prefer.
To understand what --(A2=A1) does, just enter it in an empty cell on row 2, with an= sign in front, of course, and drag it down.
