# VBA Vlookup with cumulative results

#### Excel_Blonde

##### New Member
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)
 Column Part Qty A Apple 12 B Banana 6 C Carrot 18 D Cake 0 E Biscuit 7

<tbody>
</tbody>

Active Sheet (Sheet2)
 Column Order Part Qty Shortages A 1 Banana 6 0 B 2 Apple 3 0 C 3 Apple 6 0 D 4 Banana 3 3 E 5 Apple 5 2 F 6 Cake 2 2 G 7 Apple 8 8

<tbody>
</tbody>

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

##### Well-known Member
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

##### New Member
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

##### New Member
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

##### New Member
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

##### Well-known Member
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

##### New Member
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

##### Well-known Member
Your answer isn't clear to me, so it should be 10, correct?
Maybe this:

Code:
``````[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

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

Code:
``````[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

##### Well-known Member
Sorry, forget to declare the variables.
Just add this in the beginning of the code:

Code:
``````Dim i As Long
Dim va
Dim d As Object``````