Gaviny
07-31-2022, 08:29 PM
Hi Experts,
I am trying to return a text based on a different cell value and sum, example below.
in the attached excel workbook in column B we have repeating numbers (111S,112S,113S) column C has a QTY and D has a place.
Column J has the same repeating numbers as column B but the QTY in column K is different to column C.
I want to return the value from D in column L but taking into consideration the QTY in column K.
example 1.
Column J has 111S 3 times, with a total QTY of 9, I want column L to display F in (L2) as it is looking for the same code (111S) in column B and the QTY in column C and returning the value in column D. I want P in L3 & L4) as it knows L2 has accounted for the total QTY of (111S) in the first row with (111S) so it looks for the next QTY of (111S).
Example 2.
Column J has 112S 6 times, with a total QTY of 6, I want column L to display PK in the first 5 rows (112S) is in column J, as the the first (112S) in column B has QTY 5 in PK, and on the next (112S) in in column L should be N as the first 5 have been accounted for in the first 5 (112S) in column L.
Example 3.
Column J has 113S in 1 time with a total QTY of 5, I want column L to display L, as the first time (113S) appears in column B it has a QTY smaller than the QTY in column K it would display the value from row 8 as it equals or is greater then the value in column K.
I have added some detail in the attached workbook.
Thanks in advance
Gavin
I am trying to return a text based on a different cell value and sum, example below.
in the attached excel workbook in column B we have repeating numbers (111S,112S,113S) column C has a QTY and D has a place.
Column J has the same repeating numbers as column B but the QTY in column K is different to column C.
I want to return the value from D in column L but taking into consideration the QTY in column K.
example 1.
Column J has 111S 3 times, with a total QTY of 9, I want column L to display F in (L2) as it is looking for the same code (111S) in column B and the QTY in column C and returning the value in column D. I want P in L3 & L4) as it knows L2 has accounted for the total QTY of (111S) in the first row with (111S) so it looks for the next QTY of (111S).
Example 2.
Column J has 112S 6 times, with a total QTY of 6, I want column L to display PK in the first 5 rows (112S) is in column J, as the the first (112S) in column B has QTY 5 in PK, and on the next (112S) in in column L should be N as the first 5 have been accounted for in the first 5 (112S) in column L.
Example 3.
Column J has 113S in 1 time with a total QTY of 5, I want column L to display L, as the first time (113S) appears in column B it has a QTY smaller than the QTY in column K it would display the value from row 8 as it equals or is greater then the value in column K.
I have added some detail in the attached workbook.
Thanks in advance
Gavin