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

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,717
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 8, 2018
Messages
44
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.
 

Excel_Blonde

New Member
Joined
Aug 8, 2018
Messages
44

ADVERTISEMENT

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.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,819
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 8, 2018
Messages
44

ADVERTISEMENT

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
Joined
Feb 1, 2016
Messages
3,819
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 8, 2018
Messages
44
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
Joined
Feb 1, 2016
Messages
3,819
Office Version
  1. 365
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,826
Messages
5,855,866
Members
431,771
Latest member
CoryMelth

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
Top