VBA Vlookup with cumulative results

[Excel_Blonde]

Hi There,

Another day, another challenge!

I'm working on a stock shortages report and cant figure out how to lookup the stock value in another sheet and then cumulative subtract as it moves down the list.

E.g
Stock levels (Sheet3)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column[/TD]
[TD]Part[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Apple[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Banana[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Carrot[/TD]
[TD]18[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Cake[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Biscuit[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]

Active Sheet (Sheet2)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column[/TD]
[TD]Order[/TD]
[TD]Part[/TD]
[TD]Qty[/TD]
[TD]Shortages[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[TD]Banana[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[TD]Apple[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]3[/TD]
[TD]Apple[/TD]
[TD]6[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]4[/TD]
[TD]Banana[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]5[/TD]
[TD]Apple[/TD]
[TD]5[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]6[/TD]
[TD]Cake[/TD]
[TD]2[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]7[/TD]
[TD]Apple[/TD]
[TD]8[/TD]
[TD]8[/TD]
[/TR]
</tbody>[/TABLE]

I'm not sure whether I need to have a cumulative shortage or shortage by order so both solutions would be helpful.

Any help would be appreciated.

 

[steve the fish]

Does this do it?

=MIN(D2,ABS(MIN(0,VLOOKUP(C2,Sheet3!B:C,2,0)-SUMIFS($D$1:D1,$C$1:C1,C2)-D2)))

 

[Excel_Blonde]

Does this do it?

=MIN(D2,ABS(MIN(0,VLOOKUP(C2,Sheet3!B:C,2,0)-SUMIFS($D$1:D1,$C$1:C1,C2)-D2)))

Thanks Steve, that does seem to work but now I need to try to put it into VBA. This does give me a starting point though.

Thank you very much

 

[Excel_Blonde]

Thanks Steve, that does seem to work but now I need to try to put it into VBA. This does give me a starting point though.

Thank you very much

Ok, I'm struggling with this. Can anyone help me to use this within VBA? I've tried manipulating it to get it to work but I'm just not clever enough .
Any help would be appreciated.

 

[Excel_Blonde]

Ok, I'm struggling with this. Can anyone help me to use this within VBA? I've tried manipulating it to get it to work but I'm just not clever enough .
Any help would be appreciated.

Hi All,

I made it work with the below solution:

"=MIN(RC[-8],ABS(MIN(0,VLOOKUP(RC[-12],Sheet3!R1:R1048576,13,0)-SUMIFS(R1C15:R[-1]C[-8],R1C11:R[-1]C[-12],RC[-12])-RC[-8])))"

This only shows shortages by line. What if I wanted them to accumulate (add up going down the list)? I will continue to work at it, but if anyone can offer any suggestions it would help massively.

 

[Akuini]

Hi, Excel_Blonde
I don’t quite understand what you mean by “add up going down the list”.
In order 5 Apple has shortage of 2, then in order 7 Apple has additional Qty of 8, so why does the shortage is 8? Shouldn’t it be 2 + 8 = 10?

 

[Excel_Blonde]

Hi, Excel_Blonde
I don’t quite understand what you mean by “add up going down the list”.
In order 5 Apple has shortage of 2, then in order 7 Apple has additional Qty of 8, so why does the shortage is 8? Shouldn’t it be 2 + 8 = 10?

That's how I would like it to work, that's what I meant by "add up going down the list", a cumulative figure. Any help would be appreciated.

 

[Akuini]

Your answer isn't clear to me, so it should be 10, correct?
Maybe this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1088487a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1088487-vba-vlookup-cumulative-results.html[/COLOR][/I]

[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
d.CompareMode = vbTextCompare
[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet3"[/COLOR])
va = .Range([COLOR=brown]"B2:C"[/COLOR] & .Cells(.Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
d(va(i, [COLOR=crimson]1[/COLOR])) = va(i, [COLOR=crimson]2[/COLOR])
[COLOR=Royalblue]Next[/COLOR]

va = Range([COLOR=brown]"C2:E"[/COLOR] & Cells(Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
[COLOR=Royalblue]If[/COLOR] d.Exists(va(i, [COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]Then[/COLOR]
    va(i, [COLOR=crimson]3[/COLOR]) = va(i, [COLOR=crimson]2[/COLOR]) - d(va(i, [COLOR=crimson]1[/COLOR]))
    d(va(i, [COLOR=crimson]1[/COLOR])) = d(va(i, [COLOR=crimson]1[/COLOR])) - va(i, [COLOR=crimson]2[/COLOR])
[I][COLOR=seagreen]'    If d(va(i, 1)) < 0 Then d(va(i, 1)) = 0[/COLOR][/I]
    [COLOR=Royalblue]If[/COLOR] va(i, [COLOR=crimson]3[/COLOR]) < [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] va(i, [COLOR=crimson]3[/COLOR]) = [COLOR=crimson]0[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"C2"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]3[/COLOR]) = va

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

 

[Excel_Blonde]

Your answer isn't clear to me, so it should be 10, correct?
Maybe this:

[FONT=lucida console][COLOR=Royalblue]Sub[/COLOR] a1088487a()
[I][COLOR=seagreen]'https://www.mrexcel.com/forum/excel-questions/1088487-vba-vlookup-cumulative-results.html[/COLOR][/I]

[COLOR=Royalblue]Set[/COLOR] d = CreateObject([COLOR=brown]"scripting.dictionary"[/COLOR])
d.CompareMode = vbTextCompare
[COLOR=Royalblue]With[/COLOR] Sheets([COLOR=brown]"Sheet3"[/COLOR])
va = .Range([COLOR=brown]"B2:C"[/COLOR] & .Cells(.Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]With[/COLOR]
[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
d(va(i, [COLOR=crimson]1[/COLOR])) = va(i, [COLOR=crimson]2[/COLOR])
[COLOR=Royalblue]Next[/COLOR]

va = Range([COLOR=brown]"C2:E"[/COLOR] & Cells(Rows.count, [COLOR=brown]"C"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp).Row)

[COLOR=Royalblue]For[/COLOR] i = [COLOR=crimson]1[/COLOR] [COLOR=Royalblue]To[/COLOR] UBound(va, [COLOR=crimson]1[/COLOR])
[COLOR=Royalblue]If[/COLOR] d.Exists(va(i, [COLOR=crimson]1[/COLOR])) [COLOR=Royalblue]Then[/COLOR]
    va(i, [COLOR=crimson]3[/COLOR]) = va(i, [COLOR=crimson]2[/COLOR]) - d(va(i, [COLOR=crimson]1[/COLOR]))
    d(va(i, [COLOR=crimson]1[/COLOR])) = d(va(i, [COLOR=crimson]1[/COLOR])) - va(i, [COLOR=crimson]2[/COLOR])
[I][COLOR=seagreen]'    If d(va(i, 1)) < 0 Then d(va(i, 1)) = 0[/COLOR][/I]
    [COLOR=Royalblue]If[/COLOR] va(i, [COLOR=crimson]3[/COLOR]) < [COLOR=crimson]0[/COLOR] [COLOR=Royalblue]Then[/COLOR] va(i, [COLOR=crimson]3[/COLOR]) = [COLOR=crimson]0[/COLOR]

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]If[/COLOR]
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=brown]"C2"[/COLOR]).Resize(UBound(va, [COLOR=crimson]1[/COLOR]), [COLOR=crimson]3[/COLOR]) = va

[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Hi, Thanks for your response. The is returning Compile error. Variable not defined. Referring to 'Set d =', I've had a quick look but cant see whats wrong with it?

 

[Akuini]

Sorry, forget to declare the variables.
Just add this in the beginning of the code:

Dim i As Long
Dim va
Dim d As Object