VBA Match data, find mismatch data and copy data to validation sheet. Not working correctly.

pawcoyote

Board Regular
Joined
Mar 5, 2012
Messages
89
Office Version
  1. 365
Platform
  1. Windows
Hi, I am trying to do the following with the example worksheet. I see that we cannot upload workbooks anymore. I tried the L2BB but that doesn't work for some reason.

I want to Index and Match the Serial Numbers on Site1 and Site2, then I want to look for mismatched data in the following columns on Site1 and Site2. If it finds a mismatch it copies only the data from Site1 and Site2 which will show the mismatch data. I am also looking for a way to state on the Validation sheet in the Evaluation column what is mismatched. That's a work in progress.

Site1: Yellow is mismatch data so we can see it better. Color coded the headers with information that should be copied to Validation if there is a mismatch.
site1_image.jpg


Site2: Yellow is mismatch data so we can see it better. Color coded the header with information that should be copied to Validation if there is a mismatch.
site2_image.jpg


Validation:
validation_image.jpg


Below is the code I used. It copies everything over not just the mismatch data.
VBA Code:
Sub Validation()
Dim wsSite1 As Worksheet
Dim wsSite2 As Worksheet
Dim wsValidation As Worksheet
Dim lastRowSite1 As Long
Dim lastRowSite2 As Long
Dim validationRow As Long
Dim i As Long

' Set references to worksheets
Set wsSite1 = ThisWorkbook.Worksheets("Site1")
Set wsSite2 = ThisWorkbook.Worksheets("Site2")
Set wsValidation = ThisWorkbook.Worksheets("Validation")

' Find the last row in each worksheet
lastRowSite1 = wsSite1.Cells(wsSite1.Rows.Count, "A").End(xlUp).Row
lastRowSite2 = wsSite2.Cells(wsSite2.Rows.Count, "A").End(xlUp).Row

' Initialize the row number for the Validation sheet
validationRow = 2 ' Start from row 2 to leave the header row intact

' Loop through each row in Site1
For i = 2 To lastRowSite1 ' Assuming data starts from row 2 and has headers

' Get the serial number from Site1
serialNumberSite1 = wsSite1.Cells(i, "A").Value

' Search for a matching serial number in Site2
matchingRowSite2 = Application.Match(serialNumberSite1, wsSite2.Range("A2:A" & lastRowSite2), 0)

If Not IsError(matchingRowSite2) Then
' If a match is found, compare the data
If wsSite1.Cells(i, "G").Value <> wsSite2.Cells(matchingRowSite2, "E").Value Or _
wsSite1.Cells(i, "H").Value <> wsSite2.Cells(matchingRowSite2, "F").Value Or _
wsSite1.Cells(i, "I").Value <> wsSite2.Cells(matchingRowSite2, "G").Value Or _
wsSite1.Cells(i, "J").Value <> wsSite2.Cells(matchingRowSite2, "H").Value Then

' **Copy the mismatched data to the Validation sheet**
wsSite1.Range("D" & i & ":J" & i).Copy wsValidation.Cells(validationRow, "B")
wsSite2.Range("B" & i & ":H" & i).Copy wsValidation.Cells(validationRow, "I")

validationRow = validationRow + 1 ' Move to the next row in the Validation sheet
End If
End If
Next i

' Clean up
Set wsSite1 = Nothing
Set wsSite2 = Nothing
Set wsValidation = Nothing
End Sub
 
Sorry I tried to bold and change the color of the text in the code and it added the color info from the website. It's not in the VBA source on my workbook.
 
Upvote 0

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
My suggestion: forget about
Rich (BB code):
If wsSite1.Cells(i, "G").Value <> wsSite2.Cells(matchingRowSite2, "E").Value Or _
wsSite1.Cells(i, "H").Value <> wsSite2.Cells(matchingRowSite2, "F").Value Or _
etc etc

and move to a itemized approach; ie test each pair of column and if they mismatch then report that in the wsValidation area. Something like:
Code:
For I = 2 To lastRowSite1 ' Assuming data starts from row 2 and has headers
    ' Get the serial number from Site1
    serialNumberSite1 = wsSite1.Cells(I, "A").Value
    ' Search for a matching serial number in Site2
    matchingRowSite2 = Application.Match(serialNumberSite1, wsSite2.Range("A1:A" & lastRowSite2), 0)  '?? is A the right column?

    If Not IsError(matchingRowSite2) Then
    ' If a match is found, compare the data
    'THE FOLLOWING LINES ARE EXAMPLES, USE YOUR REAL COLUMNS
        validationRow = wsValidation.Cells(Rows.Count, "A").End(xlUp).Row + 1
        If wsSite1.Cells(I, "G").Value <> wsSite2.Cells(matchingRowSite2, "E").Value Then
            wsValidation.Cells(validationRow, "A").Value = serialNumberSite1
            wsSite1.Cells(I, "G").Copy wsValidation.Cells(validationRow, "B")
        End If
        If wsSite1.Cells(I, "H").Value <> wsSite2.Cells(matchingRowSite2, "F").Value Then
            wsValidation.Cells(validationRow, "A").Value = serialNumberSite1
            wsSite1.Cells(I, "H").Copy wsValidation.Cells(validationRow, "C")
        End If
        'and so on for the remaining columns that you need to compare
        '
    End If
Next I

A more flexible way could be:
-create thre arrays that describe the columns to be compared and where the mismatch has to be reported
-for each line, loop through the items in the arrays, do the comparison, report the mismatch, if it exists.
Something like
Code:
Dim Site1Col, Site2Cols, ValidationCols, J As Long
'
'....
'
'make sure the following arrays contain all the same number of elements
Site1Cols = Array("A", "H", etc, etc)             'Columns in Site1...
Site2Cols = Array("E", "F", etc, etc)             '...to be compared with these in Site2...
ValidationCols = Array("B", "C", etc, etc)        '...and a mismatch be reported in these in wsValidation
'
'.... HERE some of your code 
'
For I = 2 To lastRowSite1 ' Assuming data starts from row 2 and has headers
    ' Get the serial number from Site1
    serialNumberSite1 = wsSite1.Cells(I, "A").Value
    ' Search for a matching serial number in Site2
    matchingRowSite2 = Application.Match(serialNumberSite1, wsSite2.Range("A1:A" & lastRowSite2), 0)  '?? is A the right column?

    If Not IsError(matchingRowSite2) Then
        validationRow = wsValidation.Cells(Rows.Count, "A").End(xlUp).Row + 1
        ' If a match is found, compare the data
        'THE FOLLOWING LINES ARE EXAMPLES, USE YOUR REAL COLUMNS
        For J = 0 To UBound(Site1Cols)
            If wsSite1.Cells(I, Site1Cols(J)).Value <> wsSite2.Cells(matchingRowSite2, Site2Cols(J)).Value Then
                wsValidation.Cells(validationRow, "A").Value = serialNumberSite1
                wsSite1.Cells(I, Site1Cols(J)).Copy wsValidation.Cells(validationRow, ValidationCols(J))
            End If
        Next J
    End If
Next I
 
Upvote 0
My suggestion: forget about
Rich (BB code):
If wsSite1.Cells(i, "G").Value <> wsSite2.Cells(matchingRowSite2, "E").Value Or _
wsSite1.Cells(i, "H").Value <> wsSite2.Cells(matchingRowSite2, "F").Value Or _
etc etc

and move to a itemized approach; ie test each pair of column and if they mismatch then report that in the wsValidation area. Something like:
Code:
For I = 2 To lastRowSite1 ' Assuming data starts from row 2 and has headers
    ' Get the serial number from Site1
    serialNumberSite1 = wsSite1.Cells(I, "A").Value
    ' Search for a matching serial number in Site2
    matchingRowSite2 = Application.Match(serialNumberSite1, wsSite2.Range("A1:A" & lastRowSite2), 0)  '?? is A the right column?

    If Not IsError(matchingRowSite2) Then
    ' If a match is found, compare the data
    'THE FOLLOWING LINES ARE EXAMPLES, USE YOUR REAL COLUMNS
        validationRow = wsValidation.Cells(Rows.Count, "A").End(xlUp).Row + 1
        If wsSite1.Cells(I, "G").Value <> wsSite2.Cells(matchingRowSite2, "E").Value Then
            wsValidation.Cells(validationRow, "A").Value = serialNumberSite1
            wsSite1.Cells(I, "G").Copy wsValidation.Cells(validationRow, "B")
        End If
        If wsSite1.Cells(I, "H").Value <> wsSite2.Cells(matchingRowSite2, "F").Value Then
            wsValidation.Cells(validationRow, "A").Value = serialNumberSite1
            wsSite1.Cells(I, "H").Copy wsValidation.Cells(validationRow, "C")
        End If
        'and so on for the remaining columns that you need to compare
        '
    End If
Next I

A more flexible way could be:
-create thre arrays that describe the columns to be compared and where the mismatch has to be reported
-for each line, loop through the items in the arrays, do the comparison, report the mismatch, if it exists.
Something like
Code:
Dim Site1Col, Site2Cols, ValidationCols, J As Long
'
'....
'
'make sure the following arrays contain all the same number of elements
Site1Cols = Array("A", "H", etc, etc)             'Columns in Site1...
Site2Cols = Array("E", "F", etc, etc)             '...to be compared with these in Site2...
ValidationCols = Array("B", "C", etc, etc)        '...and a mismatch be reported in these in wsValidation
'
'.... HERE some of your code
'
For I = 2 To lastRowSite1 ' Assuming data starts from row 2 and has headers
    ' Get the serial number from Site1
    serialNumberSite1 = wsSite1.Cells(I, "A").Value
    ' Search for a matching serial number in Site2
    matchingRowSite2 = Application.Match(serialNumberSite1, wsSite2.Range("A1:A" & lastRowSite2), 0)  '?? is A the right column?

    If Not IsError(matchingRowSite2) Then
        validationRow = wsValidation.Cells(Rows.Count, "A").End(xlUp).Row + 1
        ' If a match is found, compare the data
        'THE FOLLOWING LINES ARE EXAMPLES, USE YOUR REAL COLUMNS
        For J = 0 To UBound(Site1Cols)
            If wsSite1.Cells(I, Site1Cols(J)).Value <> wsSite2.Cells(matchingRowSite2, Site2Cols(J)).Value Then
                wsValidation.Cells(validationRow, "A").Value = serialNumberSite1
                wsSite1.Cells(I, Site1Cols(J)).Copy wsValidation.Cells(validationRow, ValidationCols(J))
            End If
        Next J
    End If
Next I
Thank you I will look at, so I learn how to do them for the future as well. The flexibility might be the way I will go. If I have not said it before, thank you for all your guidance and help!
 
Upvote 0
Thank you for the feedback
Beware, with this approach (both the flavours), the mismatch worksheet will only record the serialNumber (col A) and the mismatching columns, taking the value from wsSite1
 
Upvote 0
Thank you for the feedback
Beware, with this approach (both the flavours), the mismatch worksheet will only record the serialNumber (col A) and the mismatching columns, taking the value from wsSite1
Ah, I will test it out and see. I need the data to flow from Site1 and Site2 to the Validation sheet. The Serial Number on Site1 is in Column B and the Serial Number on Site2 is in Column P. Then the data that I need is in different columns across the sheets. Not in logical order like I initially built. That was my learning sheet in how to code it properly. Greatly appreciate all your help!
 
Upvote 0
Last suggestion:
to highlight the differences between Site1 and Site2 it could be wise that the validation sheet copy both the values, from Site1 and Site2.
Code:
VBA Code:
            If wsSite1.Cells(I, Site1Cols(J)).Value <> wsSite2.Cells(matchingRowSite2, Site2Cols(J)).Value Then
                wsValidation.Cells(validationRow, "A").Value = serialNumberSite1
'                wsSite1.Cells(I, Site1Cols(J)).Copy wsValidation.Cells(validationRow, ValidationCols(J))
                wsValidation.Cells(validationRow, ValidationCols(J)).Value = wsSite1.Cells(I, Site1Cols(J)).Value & " <#> " & wsSite2.Cells(matchingRowSite2, Site2Cols(J)).Value
            End If
The line that copied from Site1 to wsValidation is replaced by the line that concatenate both from Site1 and Site2
Now it's on you to experiment with the various hypotheses...
 
Upvote 0
Last suggestion:
to highlight the differences between Site1 and Site2 it could be wise that the validation sheet copy both the values, from Site1 and Site2.
Code:
VBA Code:
            If wsSite1.Cells(I, Site1Cols(J)).Value <> wsSite2.Cells(matchingRowSite2, Site2Cols(J)).Value Then
                wsValidation.Cells(validationRow, "A").Value = serialNumberSite1
'                wsSite1.Cells(I, Site1Cols(J)).Copy wsValidation.Cells(validationRow, ValidationCols(J))
                wsValidation.Cells(validationRow, ValidationCols(J)).Value = wsSite1.Cells(I, Site1Cols(J)).Value & " <#> " & wsSite2.Cells(matchingRowSite2, Site2Cols(J)).Value
            End If
The line that copied from Site1 to wsValidation is replaced by the line that concatenate both from Site1 and Site2
Now it's on you to experiment with the various hypotheses...
Thank you.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,772
Members
452,353
Latest member
strainu

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