Variable look up fields based on Value from different cell.

[Gaviny]

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

[p45cal]

At a first exploration this looks difficult to solve and I don't know whether I can help.
So a few questions:

What version of Excel are you using?
Would a macro be OK?
If not a macro, would a User Defined Function be OK? (It uses a macro, but you use it just like a formula.)
Is the worksheet function LAMBDA available to you?

And another:
If a new line was placed at the bottom of the right hand table being:
Code QTY
113S 1
what would be the Place?

For others, it looks like the table on the left is a stock inventory, with the items being Code, the Place being a location/bin and QTY being the count of those items in the bin.
The table on the right is the demand for stock and the OP wants to know which bin he can fetch the entire stock from to fulfil one row's demand (no picking items from 2 bins to fulfil the demand on one row).
It looks like there's a preference to take stock from the higher rows of the inventory first, and the demand table on the right seems to be processed top down too.

[Gaviny]

At a first exploration this looks difficult to solve and I don't know whether I can help.
So a few questions:

What version of Excel are you using?
Would a macro be OK?
If not a macro, would a User Defined Function be OK? (It uses a macro, but you use it just like a formula.)
Is the worksheet function LAMBDA available to you?

And another:
If a new line was placed at the bottom of the right hand table being:
Code QTY
113S 1
what would be the Place?

For others, it looks like the table on the left is a stock inventory, with the items being Code, the Place being a location/bin and QTY being the count of those items in the bin.
The table on the right is the demand for stock and the OP wants to know which bin he can fetch the entire stock from to fulfil one row's demand (no picking items from 2 bins to fulfil the demand on one row).
It looks like there's a preference to take stock from the higher rows of the inventory first, and the demand table on the right seems to be processed top down too.

Hi P45cal, thanks for responding.
Firstly let me apologies for not including the information in my original post.
I am working with version 365.
A macro will be fine
I don't have LAMBDA
If a new line was added with the 113S code it would be PK

Thanks,
Gavin

[p45cal]

If your version is O365 then I'm surprised you don't have LAMBDA. You should also have LET. Are you up to date?
I'll try putting together a UDF, then maybe a formula.
Was my interpretation 'For others…" accurate?
It's 11pm where I am so I'll look at this tomorrow sometime.

[Gaviny]

Hi,
You are correct I do have LAMBDA and LET.
Your interpretation is frightening accurate :clap:
Once again thanks for your help.

[p45cal]

Check the attached. A single cell formula in K3:

=PickLocations(H3:I13,B3:D9)
If you bring up the Function Arguments dialogue it's obvious where things go in the formulaL
30026

[Gaviny]

Mate, this is excellent.
Thank you so much for your help