none
Matching using VBA in Excel RRS feed

  • Question

  • Hello, Please can you advise on the below.

    I am trying to match the “SALE DOC NO” and “LINE ITEM” from REPORT1, to “SALE DOC NO” and “LINE ITEM” on REPORT2, and output the matching results with “PURCHASING ORDER” & “PO”

    Report 1

    

    Report 2


    Output



    • Edited by FENGE2 Wednesday, May 3, 2017 11:57 AM
    Wednesday, May 3, 2017 11:53 AM

Answers

  • I don't know how much data you have but it sounds like you should use a database.  You could do this with Excel and ADODB. However, this is more involved and requires learning SQL.  This is how I would do it.  You can do very complex queries.  A simple approach is to use a data dictionary (hash table).  Here is an example.

    Option Explicit
    
    Sub UpdateTable()
    ''Reference Microsoft Scripting Runtime
     Dim d As New Scripting.Dictionary
     Dim i As Integer, j As Integer, k As Integer
     Dim ws1 As Worksheet
     Dim ws2 As Worksheet
     Dim ws3 As Worksheet
     Dim key As String
     
     Set ws1 = Worksheets("Report1")
     Set ws2 = Worksheets("Report2")
     Set ws3 = Worksheets("Output")
     ' Create dictionary
     i = 2
     While ws1.Cells(i, 1) <> ""
       key = ws1.Cells(i, 1).Value & "|" & ws1.Cells(i, 2).Value
       d.Add key, i
       i = i + 1
     Wend
     
     i = 2
     j = 2
     While ws2.Cells(i, 1) <> ""
       key = ws2.Cells(i, 1).Value & "|" & ws2.Cells(i, 2).Value
       If d.Exists(key) Then
         For k = 1 To 4
           ws3.Cells(j, k) = ws2.Cells(i, k)
         Next k
         j = j + 1
       End If
       i = i + 1
     Wend
    End Sub
    


    • Marked as answer by FENGE2 Friday, April 13, 2018 1:11 PM
    Thursday, May 4, 2017 3:06 PM

All replies

  • Hi FENGE2,

    If combination of "SALES DOC NO" & "LINE ITEM" is unique in each sheet, 
    (1) loop row 2 and last row in Report1
    (2) "Find" the same "SALES DOC NO" in Report2
    (3) when found, compare "LINE ITEM" between Report1 and Report2 
    (4) if (3) satisfied, put data in Output

    # I suppose Report1, Report2, and Output is a sheet. Are they files?
    ___________
    Ashidacchi

    Wednesday, May 3, 2017 11:25 PM
  • They are tabs of one workbook, does that make this any easier?
    • Edited by FENGE2 Thursday, May 4, 2017 12:50 PM
    Thursday, May 4, 2017 12:49 PM
  • I don't know how much data you have but it sounds like you should use a database.  You could do this with Excel and ADODB. However, this is more involved and requires learning SQL.  This is how I would do it.  You can do very complex queries.  A simple approach is to use a data dictionary (hash table).  Here is an example.

    Option Explicit
    
    Sub UpdateTable()
    ''Reference Microsoft Scripting Runtime
     Dim d As New Scripting.Dictionary
     Dim i As Integer, j As Integer, k As Integer
     Dim ws1 As Worksheet
     Dim ws2 As Worksheet
     Dim ws3 As Worksheet
     Dim key As String
     
     Set ws1 = Worksheets("Report1")
     Set ws2 = Worksheets("Report2")
     Set ws3 = Worksheets("Output")
     ' Create dictionary
     i = 2
     While ws1.Cells(i, 1) <> ""
       key = ws1.Cells(i, 1).Value & "|" & ws1.Cells(i, 2).Value
       d.Add key, i
       i = i + 1
     Wend
     
     i = 2
     j = 2
     While ws2.Cells(i, 1) <> ""
       key = ws2.Cells(i, 1).Value & "|" & ws2.Cells(i, 2).Value
       If d.Exists(key) Then
         For k = 1 To 4
           ws3.Cells(j, k) = ws2.Cells(i, k)
         Next k
         j = j + 1
       End If
       i = i + 1
     Wend
    End Sub
    


    • Marked as answer by FENGE2 Friday, April 13, 2018 1:11 PM
    Thursday, May 4, 2017 3:06 PM