VBA Vlookup with cumulative results

Excel_Blonde

New Member
Joined
Aug 8, 2018
Messages
44
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)
ColumnPartQty
AApple12
BBanana6
CCarrot18
DCake0
EBiscuit7

<tbody>
</tbody>

Active Sheet (Sheet2)
ColumnOrderPartQtyShortages
A1Banana60
B2Apple30
C3Apple60
D4Banana33
E5Apple52
F6Cake22
G7Apple88

<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.
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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 :confused: .
Any help would be appreciated.
 
Upvote 0
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 :confused: .
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top