Comparing 2 Lists and using Vlookups with multiple rows.

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
119
https://drive.google.com/open?id=1TfqRi0g6FwDCY0iNOfVWOyLhAXgBPygR


I'm trying to write a VBA sub to look up the value of two references on different sheets. The problem I've got is the value being looked up has entries in more than one row which I believe is difficult for this command.

I was hoping to use a given number, look it up and gain every quantity for the value with a date range that hasn't occurr
ed yet (highlighted in yellow on attached picture).

Has anyone got any advice on the best way to do this.

So to clarify I need a number from Sheet B (which also has a total quantity) and look it up in the table pictured adding up all the values from dates in the past (which in this example would total 2,030,938). I know what you are thinking, I'm not asking for much. I've tried to learn everything from other resources but it's proved to be too much for my relatively new VBA knowledge base.

Thanks in advance for any help.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
Can you give an example of the value you want to look for?
 

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
119
The following (sorry but adding image doesn't work) is the original table which looks up the value in column D and checks in the other table (the one from my original post) and enters the total quantity available (date is older than NOW) into a new colum at the end:

https://drive.google.com/open?id=1psqKBmGuZLtTYBHFM9ZDHi_6eI2m2yz7

seems a bit of a complex one but any help would be appreciated.

Basically the value I'm looking up is a Purchase Order number. The items are delivered over staged intervals and I need to check that the quantity on the Suppliers Spreadsheet matches what has been ordered factoring in the expected delivery dates. The suppliers sheet just has the total number on the order and how many have been delivered (Source Table) so far. The other sheet (Excel Look up) is whats on our in-house system and is split up with a new line for every staggered delivery date. I hope that makes sense.
 
Last edited:

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
Try this, results in sheet1 column "P".
Change data in red by your information.

Code:
Sub Comparing_List()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim u As Long, i As Long, wSuma As Double
    Dim r As Range, b As Range, celda As String
    
    Set sh1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
    
    u = sh1.Range("D" & Rows.Count).End(xlUp).Row
    For i = 3 To u
        wSuma = 0
        Set r = sh2.Columns("A")
        Set b = r.Find(sh1.Cells(i, "D"), LookAt:=xlWhole, LookIn:=xlValues)
        If Not b Is Nothing Then
            celda = b.Address
            Do
                If sh2.Cells(b.Row, "E").Value < Date Then
                    wSuma = wSuma + sh2.Cells(b.Row, "D").Value
                End If
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And b.Address <> celda
        End If
        sh1.Cells(i, "[COLOR=#ff0000]P[/COLOR]").Value = wSuma
    Next
    MsgBox "End"
End Sub
 

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
119
Wow, thanks! Works like a dream, I was ready to give up on that. Was so easy to copy and paste straight into my sub. I can't thank you enough for that.

Can you recommend any good books to learn VBA? I've still got so much to learn but not sure of the best way (apart from the good advice on here of course). I've read the Dummies book but haven't been able to find any suitable courses at my local college even, they only go up to Advanced Excel (Pivot tables etc).

Thanks again for all your help you've given me up to now.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
I'm glad to help you. I appreciate your kind comments.

The best thing is to learn in the forums, everything starts with your need and search on that topic. The courses are good but if you do not use them they did not work.
 

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
119
Yea true, its always easier to learn something that's relevant to want you need. Thanks again for your help.
 

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
119
Try this, results in sheet1 column "P".
Change data in red by your information.

Code:
Sub Comparing_List()
    Dim sh1 As Worksheet, sh2 As Worksheet
    Dim u As Long, i As Long, wSuma As Double
    Dim r As Range, b As Range, celda As String
    
    Set sh1 = Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]Sheet2[/COLOR]")
    
    u = sh1.Range("D" & Rows.Count).End(xlUp).Row
    For i = 3 To u
        wSuma = 0
        Set r = sh2.Columns("A")
        Set b = r.Find(sh1.Cells(i, "D"), LookAt:=xlWhole, LookIn:=xlValues)
        If Not b Is Nothing Then
            celda = b.Address
            Do
                If sh2.Cells(b.Row, "E").Value < Date Then
                    wSuma = wSuma + sh2.Cells(b.Row, "D").Value
                End If
                Set b = r.FindNext(b)
            Loop While Not b Is Nothing And b.Address <> celda
        End If
        sh1.Cells(i, "[COLOR=#ff0000]P[/COLOR]").Value = wSuma
    Next
    MsgBox "End"
End Sub
I've just been troubleshooting a problem I've had and some of it has come from this code not drawing off certain rows which seems weird. The information it draws from is definitely there so I'm not sure why it doesn't. I've tried stepping through the code but I don't really understand most of it if I'm honest so I was wondering whether you would be able to offer any help with it please?

One example is row 10 in the "Graphic" sheet in the file below. The button at the top runs the macro.

https://www.dropbox.com/s/v6f4ypsaxa8747v/Stock Sheet Checker Graphic Fail.xlsm?dl=0
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,735
Office Version
2007
Platform
Windows
The macro has the condition of adding only the values ​​that are less than today's date.
In your example, row 10 is dated May 17, May 17 is not less than May 7, so do not add that value.
 

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
119
OK I understand, I've just read the original post and realised that's what I put in the criteria. I've only just realised that it causes problems when a supplier decides to deliver early.

How do I remove this part and just make it calculate the difference regardless of date as I later discovered it isn't relevant (probably why I forgot I'd originally asked for it, whoops sorry).

Thanks
 

Forum statistics

Threads
1,081,545
Messages
5,359,438
Members
400,528
Latest member
Ratish52

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top