Find & replace: Find ID from Sh1 if it exist Sh2 in then replace collum A B C D with values from Sh1

MicrosoftNewbie121 

You can use the following VBA code to achieve what you described. This macro will go through each row in Sheet1, check if the ID exists in Sheet2, and then replace or create a new row accordingly.

Vba Code is untested, please backup your file before you use it.

Sub UpdateData()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim lastRow1 As Long, lastRow2 As Long
    Dim idCol1 As Range, idCol2 As Range
    Dim searchID As Variant, foundCell As Range
    
    ' Set references to the worksheets
    Set ws1 = ThisWorkbook.Sheets("Sheet1")
    Set ws2 = ThisWorkbook.Sheets("Sheet2")
    
    ' Assuming your IDs are in column B
    lastRow1 = ws1.Cells(ws1.Rows.Count, "B").End(xlUp).Row
    lastRow2 = ws2.Cells(ws2.Rows.Count, "B").End(xlUp).Row
    
    ' Assuming your IDs start from row 2
    Set idCol1 = ws1.Range("B2:B" & lastRow1)
    Set idCol2 = ws2.Range("B2:B" & lastRow2)
    
    ' Loop through each ID in Sheet1
    For Each searchID In idCol1
        ' Search for the ID in Sheet2
        Set foundCell = idCol2.Find(searchID.Value, LookIn:=xlValues)
        
        If Not foundCell Is Nothing Then
            ' ID exists in Sheet2, replace values in Sheet2
            foundCell.Offset(0, -1).Resize(1, 4).Value = searchID.Offset(0, -1).Resize(1, 4).Value
        Else
            ' ID doesn't exist in Sheet2, create a new row in Sheet2
            lastRow2 = lastRow2 + 1
            ws2.Cells(lastRow2, 1).Resize(1, 4).Value = searchID.Offset(0, -1).Resize(1, 4).Value
            ws2.Cells(lastRow2, 2).Value = searchID.Value
        End If
    Next searchID
    
    MsgBox "Data updated successfully!", vbInformation
End Sub

This code assumes that the IDs are in column B for both Sheet1 and Sheet2. You can modify the code accordingly if your IDs are in a different column. To run the macro, press Alt + F8, select UpdateData, and click Run.

You can achieve this using Excel formulas and features like VLOOKUP or INDEX/MATCH too. However, keep in mind that these formulas might not be as efficient or straightforward as a VBA solution, especially when dealing with inserting new rows dynamically.

Assuming your data is in columns A to D in both Sheet1 and Sheet2, and the IDs are in column B, you can use the following steps:

1. Insert a Helper Column in Sheet1:
• In cell E2 of Sheet1, use the formula: =IF(ISNUMBER(MATCH(B2, Sheet2!B:B, 0)), "Update", "Create").
• Drag this formula down for all rows.
2. Filter Data in Sheet1:
• Apply a filter to the data in Sheet1 based on the values in column E.
• Filter for "Update" or "Create" as needed.
3. Update Existing Rows (VLOOKUP):
• In cell F2 (assuming your data starts from row 2), use the formula:

=IF(E2="Update", VLOOKUP(B2, Sheet2!B:D, {2,3,4}, FALSE), "")

• Drag this formula for all rows.
1. Insert New Rows:
• Filter for "Create" in column E.
• Copy the visible cells (excluding the header) from columns A to D.
• Go to Sheet2 and paste the data starting from the first empty row.
2. Remove Helper Column:
• You can now remove the helper column in Sheet1 (column E).

This approach uses Excel formulas and features without VBA, but it involves manual steps like filtering and copying. If your dataset is large or if you need to automate the process further, VBA would be a more efficient solution. The text, steps and functions were created with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.