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.
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.
Validation:
Below is the code I used. It copies everything over not just the mismatch data.
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.
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.
Validation:
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
