find matches between two workbooks

Question

I have two data workbooks. One dataset is of refused orders and the other dataset is for current orders. I want to find if i can match orders so that i can utilize the orders that I have in refused file. This way i wont have to make the current order and can simultaneously reduce my stack of orders that have been refused by customers. Here is my Data sheets for refused and current/printed orders. Current/Printed Orders Here is datasheet for the refused orders. Refused Orders

I need to match orders on three things. First the design name needs to match, the product name needs to match and the size needs to match in order to get an "order match".

How can I use excel vba to find matches and create a new excel worksheet in the current order workbook that can show the orders that match between both data sets. The final data output would be order number against order number from both the files.

I am just beginning to learn vba but this is a complex problem that i can not solve. Please help. I wrote a code but it does not run. It says object not defined. Code that i wrote is :

Sub Comparetwosheets()

Dim ws1 As Worksheet, ws2 As Worksheet Dim ws1row As Long, ws2row As Long, w1scol As Integer, ws2col As Integer Dim report As Worksheet Dim row As Long, col As Integer Dim R1 As Range Set R1 = Union(col(5), col(7), col(10))

Set report = Worksheet.Add

'Set numrows = number of rows of data

NumRows = Range("A1", Range("A1").End(xlDown)).Rows.Count ' Select cell a1. Range("A1").Select ' Establish "For" loop to loop "numrows" number of times. For x = 1 To NumRows x = 2 Do While x < NonBlank

   x = x + 1
   
  Do While (ws1.R1 = ws2.R1)
  
  If ws1.rw2 = ws2.rw2 Then
  report.Cells(1, 1).Value = "Match"
  
  Else: x = x + 1
  
  Loop
  
  Loop
 

'Selects cell down 1 row from active cell. ActiveCell.Offset(1, 0).Select

End Sub

Answer 1

This should be able to do it for you. You are able to set the following variables in the CompareWorksheet subroutine to what you need then to be (dataSet1, dataSet2, colPos1, colPos2, rowStart1, rowStart2). I am using a random dataset from the world bank.

Sub CompareWorksheet()
    Dim dataSet1, dataSet2 As Variant
    Dim workbook1, workbook2 As String
    Dim worksheet1, worksheet2 As String
    Dim rowStart1, rowStart2 As Integer
    
    'Get the data into the dataSet variable using a function that goes through each workbook/sheet
    workbook1 = "dashboard-data-latest1.xlsx"
    worksheet1 = "2. Harmonized Indicators"
    dataSet1 = SheetToDataSet(workbook1, worksheet1)
    
    'Get the data into the dataSet variable using a function that goes through each workbook/sheet
    workbook2 = "dashboard-data-latest.xlsx"
    worksheet2 = "2. Harmonized Indicators"
    dataSet2 = SheetToDataSet(workbook2, worksheet2)
    
    'Set this do what columns you are interested in comparing
    colPos1 = Array(1, 2, 3)
    colPos2 = Array(1, 2, 3)
    
    'Set for where you want to start 1 would be row 1/now Header.
    rowStart1 = 2
    rowStart2 = 2
    
    'Compares the dataSets
    Compare2Sheets dataSet1, dataSet2, colPos1, colPos2, rowStart1, rowStart2


End Sub

Function Compare2Sheets(dataSet1 As Variant, dataSet2 As Variant, colPos1 As Variant, colPos2 As Variant, rowStart1 As Variant, rowStart2 As Variant)
    If UBound(colPos1) = UBound(colPos2) Then
        For I = rowStart1 To UBound(dataSet1, 1)
            For j = rowStart2 To UBound(dataSet2, 1)
                matchFlag = 0
                For k = 0 To UBound(colPos1)
                   If dataSet1(I, colPos1(k)) = dataSet2(j, colPos2(k)) Then
                        matchFlag = matchFlag + 1
                   End If
                Next k
                If matchFlag = (UBound(colPos1) + 1) Then
                    Debug.Print ("Match found in Workbook 1 at row " & I & "  and Workbook 2 at row " & j)
                End If
                
            Next j
        Next I
    End If
   
End Function

Function SheetToDataSet(workbookName As Variant, worksheetName As Variant) As Variant
    'SET PAGE CHARACTERISTICS
        Application.Calculation = xlCalculationManual
        Application.ScreenUpdating = False
    'DECLARE VARIABLE
        Dim x_matrix As Range
        Dim x_copyrange As String
        Dim length, lastColumn As Integer
    'DEFINE VARIABLE
        Workbooks(workbookName).Worksheets(worksheetName).Activate
        length = 0
        lastColumn = 0
        For I = 1 To 10
            If length < Workbooks(workbookName).Worksheets(worksheetName).Cells(Rows.Count, I).End(xlUp).Row Then
                length = Workbooks(workbookName).Worksheets(worksheetName).Cells(Rows.Count, I).End(xlUp).Row
            End If
            If lastColumn < Workbooks(workbookName).Worksheets(worksheetName).Cells(I, Columns.Count).End(xlToLeft).Column Then
                lastColumn = Workbooks(workbookName).Worksheets(worksheetName).Cells(I, Columns.Count).End(xlToLeft).Column + 10
            End If
        Next I
        'Let x_copyrange = .Range(.Cells(1, 1), .Cells(length, lastColumn))
    'Return
        SheetToDataSet = Workbooks(workbookName).Worksheets(worksheetName).Range(Cells(1, 1), Cells(length, lastColumn))
End Function