Reduce subtract a value in a column if a value repeats in another column

Hi Guys,

I apologize if the Title is confusing as I didn't know how to explain the situation better in one line.

I have attached a sample file in which I have the columns, A, B, D, E, F, L and M. The first cell in F should check if M<D, if TRUE, it should return E+L-B, else E-B. But going downwards in Column F, the formula should check if values in Column A are repeating and if they are repeating it should first do the math E-B (Value in B should be from its previous usage) and then do the math similar to how it did before ( if M<D, if TRUE, it should return E+L-B, else E-B )

For ex: in the attached file, 102 is a value in Column A that repeats three times with an initial On Hand of 10 (Column E). It is being used in three different jobs 301, 306, 304 in Column C. So, in column F when the formula does the math, it will return 5 (as 5 parts are used in the job 301). Then as we down in Column F, when 102 appears again, it should consider that it now only has 5 On Hand because the other 5 is already used. Then it can do the math for M<D, if TRUE, it should return E+L-B, else E-B.

I tried my best to explain the situation above. If you guys think it is still not clear and have any questions, please feel free to ask.

Any help is appreciated

Thanks

VJ

Originally Posted by protonLeah

Your explanation is out of sync with the workbook, "sheet data"

My Apologies. Here is the correct file.

VJ

Hi Trevor,

Thanks for the response. I tried the formula but it doesn't take into account that part#102 initially has On Hand Qty of 10 and after 5 of them being used in Job#301 , it is still considering it as 10 for Job#306. (where as it should be 5). That is the logic I'm trying to apply here.


VJ

Hi JOhn,

Appreciate the help. I am still not getting the expected output. I think my explanation is confusing. So first step for me is to calculate and return Remaining OH quantity (for now, lets ignore comparing Column M and Column D). For ex: Part# 102 repeats three times in three different jobs. Their current OH in Column E is 10 (not 30 as they are duplicates because they are being used three time in different jobs). This current OH will change because the Part# 102 is used first in Job 301 (5 qtys used). So, when it gets to job 306 the Total OH should change to 5 as 5 of them have already been used and so on.

I hope that makes sense. Please let me know if you have any questions

VJ