Find next bigger value in two-way lookup

[Skovgaard]

Hello Experts,

Hope you can help.
Below table shows a running balance on a stock in quantities.
My goal is to find, when a certain order can be delivered.

Using two Xlookup-functions, I'm able to find the result, if there's enough pcs on stock.
But if there isn't enough items on stock, then I need to find when there will be.

Example:
- Requested delivery date from customer: 2021-33
- Item1
- 5 pcs

Correct result should be "2021-35", hence complete delivery can be fulfilled.

Hope you understand the challenge, otherwise don't hesitate to write your questions.

Running stock balance:

Item no.: / YYYY-Week	2021-31	2021-32	2021-33	2021-35
Item 1	10	0	3	25
Item 2	5	10	15	12
Item 3	0	0	10	15

/Skovgaard

 

[Peter_SSs]

Something like this?

Skovgaard.xlsm
	A	B	C	D	E
1	Running stock balance:
2	Item no.: / YYYY-Week	2021-31	2021-32	2021-33	2021-35
3	Item 1	10	0	3	25
4	Item 2	5	10	15	12
5	Item 3	0	0	10	15
6
7
8
9	Requested Delivery Date	2021-33	2021-32	2021-31	2021-32
10	Item	Item 1	Item 3	Item 2	Item 2
11	Qty	5	12	3	20
12	Delivery	2021-35	2021-35	2021-31	#N/A
Sheet1

Cell Formulas
Range	Formula
B12:E12	B12	=LET(f,FILTER(FILTER($B2:$E5,$B2:$E2>=B9),($A2:$A5=B10)+($A2:$A5=$A2)),INDEX(INDEX(f,1,0),MATCH(TRUE,INDEX(f,2,0)>=B11,0)))

 

[Skovgaard]

Something like this?

Skovgaard.xlsm
	A	B	C	D	E
1	Running stock balance:
2	Item no.: / YYYY-Week	2021-31	2021-32	2021-33	2021-35
3	Item 1	10	0	3	25
4	Item 2	5	10	15	12
5	Item 3	0	0	10	15
6
7
8
9	Requested Delivery Date	2021-33	2021-32	2021-31	2021-32
10	Item	Item 1	Item 3	Item 2	Item 2
11	Qty	5	12	3	20
12	Delivery	2021-35	2021-35	2021-31	#N/A
Sheet1

Cell Formulas
Range	Formula
B12:E12	B12	=LET(f,FILTER(FILTER($B2:$E5,$B2:$E2>=B9),($A2:$A5=B10)+($A2:$A5=$A2)),INDEX(INDEX(f,1,0),MATCH(TRUE,INDEX(f,2,0)>=B11,0)))

Hi Peter,

Thanks for your reply.
First time I see the function "let", seems pretty cool

I've testet your formula and I see one problem. My running stock balance is in a pivot table, so it seems that the filter function won't work on that.
Any way to solve this?

/Skovgaard

 

[Peter_SSs]

It works for me running off a PT.
Here is my dummy sample data, PT & formula results.

Skovgaard.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Item	Num	YYY-Week		Sum of Num	YYY-Week
2	Item 1	0	2021-32		Item	2021-31	2021-32	2021-33	2021-35	Grand Total
3	Item 1	0	2021-31		Item 1	2	0	3	6	11
4	Item 1	1	2021-33		Item 2	5	3	4	3	15
5	Item 2	4	2021-33		Item 3	1	8	6	10	25
6	Item 1	2	2021-31		Grand Total	8	11	13	19	51
7	Item 2	1	2021-31
8	Item 1	0	2021-35
9	Item 2	1	2021-35
10	Item 3	0	2021-33
11	Item 3	0	2021-35
12	Item 3	5	2021-33
13	Item 2	0	2021-33
14	Item 2	0	2021-33		Requested Delivery Date	2021-33	2021-32	2021-31	2021-32
15	Item 3	0	2021-32		Item	Item 1	Item 3	Item 2	Item 2
16	Item 3	1	2021-33		Qty	5	9	3	20
17	Item 1	2	2021-33		Delivery	2021-35	2021-35	2021-31	#N/A
18	Item 2	0	2021-32
19	Item 2	3	2021-32
20	Item 2	4	2021-31
21	Item 1	4	2021-35
22	Item 3	0	2021-32
23	Item 3	1	2021-32
24	Item 2	2	2021-35
25	Item 3	7	2021-32
26	Item 1	2	2021-35
27	Item 3	1	2021-31
28	Item 2	0	2021-32
29	Item 2	0	2021-33
30	Item 3	10	2021-35
31
Sheet2

Cell Formulas
Range	Formula
F17:I17	F17	=LET(f,FILTER(FILTER($F2:$I5,$F2:$I2>=F14),($E2:$E5=F15)+($E2:$E5=$E2)),INDEX(INDEX(f,1,0),MATCH(TRUE,INDEX(f,2,0)>=F16,0)))

 

[Skovgaard]

It works for me running off a PT.
Here is my dummy sample data, PT & formula results.

Skovgaard.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Item	Num	YYY-Week		Sum of Num	YYY-Week
2	Item 1	0	2021-32		Item	2021-31	2021-32	2021-33	2021-35	Grand Total
3	Item 1	0	2021-31		Item 1	2	0	3	6	11
4	Item 1	1	2021-33		Item 2	5	3	4	3	15
5	Item 2	4	2021-33		Item 3	1	8	6	10	25
6	Item 1	2	2021-31		Grand Total	8	11	13	19	51
7	Item 2	1	2021-31
8	Item 1	0	2021-35
9	Item 2	1	2021-35
10	Item 3	0	2021-33
11	Item 3	0	2021-35
12	Item 3	5	2021-33
13	Item 2	0	2021-33
14	Item 2	0	2021-33		Requested Delivery Date	2021-33	2021-32	2021-31	2021-32
15	Item 3	0	2021-32		Item	Item 1	Item 3	Item 2	Item 2
16	Item 3	1	2021-33		Qty	5	9	3	20
17	Item 1	2	2021-33		Delivery	2021-35	2021-35	2021-31	#N/A
18	Item 2	0	2021-32
19	Item 2	3	2021-32
20	Item 2	4	2021-31
21	Item 1	4	2021-35
22	Item 3	0	2021-32
23	Item 3	1	2021-32
24	Item 2	2	2021-35
25	Item 3	7	2021-32
26	Item 1	2	2021-35
27	Item 3	1	2021-31
28	Item 2	0	2021-32
29	Item 2	0	2021-33
30	Item 3	10	2021-35
31
Sheet2

Cell Formulas
Range	Formula
F17:I17	F17	=LET(f,FILTER(FILTER($F2:$I5,$F2:$I2>=F14),($E2:$E5=F15)+($E2:$E5=$E2)),INDEX(INDEX(f,1,0),MATCH(TRUE,INDEX(f,2,0)>=F16,0)))

You're right, I misspelled a range, it also works for me too.

We miss one important thing in the formula, negative balance protection.
Each time we put in a delivery date, the running stock balance must be updated with this. I've already a macro doing this, that's not the challenge.

E.g. I've made small changes to your dummy sample item 2, see below.
The first order (qty=6) will be set to week 32 and the second to week 31, total 8 pcs of item 2.

Do you understand what I mean?
Is it possible to check for future negative balance, in order to prevent this?

PS. For each cell I do this formula, I'll paste the values and update the pivot, before I go to next cell.

/Skovgaard

 

[Peter_SSs]

Do you understand what I mean?

No

 

[Skovgaard]

No

I guessed it wasn't that good explained, I'll try again with below example
This is just how I imagine it could work. Other possible solutions are welcome.

Start:
1. One table showing in (and out) transactions on stock
a. Pivot table showing running balance of stock

Open Orders:
2. Check of availability week in below column F (your original formula)
a. Formula is typed in F2 - Result week 2021-17 (order Qty=7)
b. The 7 pcs will be stored in stock transaction table (row 6 in this example - Not showed)
c. PT will be updated with new running balance - Balance in week 2021-17 is now 3
d. Point a-c is repeated for F3+F4
c. Balance in week 2021-12 is now 0. For 2021-17 it is -2, which isn't possible (negative balance)

So I was wondering if there was a way to prevent, future balances go in minus?

/Skovgaard

 

[Skovgaard]

I've tried to implement Peters "LET" function in VBA as below, but it doesn't seems to work.

PivotData, PivotWeek and PivotItem are names for defined variable areas in the workbook.
Is it not possible to do it like this, or should I define the names in the sub instead?

For Each cell In rng
        If cell.Value = "" Then
            cell.FormulaR1C1 = _
                                "=Let(f,Filter(PivotData,PivotWeek>=RC[-1]),(PivotItem=RC[-23])+(PivotItem=Lager!R11C[-17])),Index(Index(f,1,0),Match(TRUE,Index(f,2,0)>RC[-2],0)))"

/Skovgaard

 

[Peter_SSs]

Pretty hard to tell without all the details, sample data, layout, names etc, but the formula does not look a lot like what I suggested.
For a start:
Mine has two 'Filter's, yours has one
The two blue items are identical for me, not so for you.

=Let(f,Filter(PivotData,PivotWeek>=RC[-1]), ....
=LET(f,FILTER(FILTER($F2:$I5,$F2:$I2>=F14), ...

 

[Skovgaard]

Pretty hard to tell without all the details, sample data, layout, names etc, but the formula does not look a lot like what I suggested.
For a start:
Mine has two 'Filter's, yours has one
The two blue items are identical for me, not so for you.

=Let(f,Filter(PivotData,PivotWeek>=RC[-1]), ....
=LET(f,FILTER(FILTER($F2:$I5,$F2:$I2>=F14), ...

Argh... I missed the on "Filter" function, now it puts the formula into my cell.
One problem, it puts in two "@", see below.

Why is that, and can that be avoided?

/Skovgaard