Two Criteria Xlookup with If & Else Statement-CodePudding

I am trying to write a code at VBA, where my main goal is double check between two tables on two separate sheets. I made very basic two tables that have a similar structure with my data(I have 150 lines at my original data so definitely need a vba solution)

Data on Sheet 1:

From the above image example :
Table in Sheet1 doesn't have ID 12345 and ID 456789 value has a different RELEASE

Sub test1()

Set sh1 = Sheets("Sheet1")
Set rg1 = sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
Set sh2 = Sheets("Sheet2")
Set rg2 = sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))

For Each cell In rg2
    Set LR = sh1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Set c = rg1.Find(cell.Value, lookat:=xlWhole)
        If c Is Nothing Then
            Range(cell, cell.Offset(0, 2)).Copy Destination:=LR
            Range(LR, LR.Offset(0, 2)).Interior.Color = vbCyan
        Else
            If cell.Offset(0, 1).Value <> c.Offset(0, 1).Value _
                Then Range(cell, cell.Offset(0, 2)).Copy Destination:=c: _
                    Range(c, c.Offset(0, 2)).Interior.Color = vbYellow
        End If
Next

End Sub

The code above do these things:

add a new record to table_sheet1 from table_sheet2 if table_sheet1 doesn't have it.
Put a blue color to identify that this is a new record.
update existing record in table_sheet1 if it's ID match with the ID in table_sheet2 but it's RELEASE don't match with the RELEASE in table_sheet2.
Put a yellow color to identify that this is an update of the existing record.

The code assumed that table_sheet2 either has the same row data or more data than table_sheet1.

Another thing that I figure out what you mean:
The first condition of table_sheet1 has a blank STAT.
A. IF the ID and the RELEASE table_sheet1 are the same with table_sheet2, THEN fill the STAT value in table_sheet1 from table_sheet2.
B. IF the ID table_sheet1 is the same with the ID table_sheet2 but the RELEASE table_sheet1 is not the same with the RELEASE table_sheet2 THEN add a new record from table_sheet2 to table_sheet1. So here there will be a new row with a duplicate ID value in table_sheet1.
C. IF there is a data ID in table_sheet2 which not exist in table_sheet1 THEN add a new record from this ID of table_sheet2 to table_sheet1. So here there will be a new row with a new ID in table_sheet1.

After running the code:

Sub test2()

Set sh1 = Sheets("Sheet1")
Set rg1 = sh1.Range("A2", sh1.Range("A" & Rows.Count).End(xlUp))
Set sh2 = Sheets("Sheet2")
Set rg2 = sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))

For Each cell In rg2
    Set LR = sh1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    Set c = rg1.Find(cell.Value, lookat:=xlWhole)
        If c Is Nothing Then
            Range(cell, cell.Offset(0, 2)).Copy Destination:=LR
        Else
            If cell.Offset(0, 1).Value <> c.Offset(0, 1).Value Then
                Range(cell, cell.Offset(0, 2)).Copy Destination:=LR
            Else
                Range(cell, cell.Offset(0, 2)).Copy Destination:=c
            End If
        End If
Next

End Sub

Still not sure though if that's what you mean.

CodePudding user response：

To be honest your second code is exactly what I wanted to do!

Thank you so so much.

I was able to write the first part of the code but the parts with the 'else' statement was new and a bit blur for me, your answers indeed helps!