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

[MicrosoftNewbie121]

Hello, can you please help me ? i would like to do this in MACRO VBA for learning

I would like to find ID from Sheet 1 collum "B" check :

- if ID exists in Sheet 2 collum "B" then replace Sheet 2 collum "A", "B", "C", "D" with informations from Sheet 1 "A", "B", "C", "D"

- if ID doesn't exists in Sheet 2 then create a new row with informations from workbook 1 "A", "B", "C", "D"

- Loop this until last row

(Both Sheets use same collum name for the same items)

Thank you forward for the help ^^

[#2 · Accepted solution]

@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.

[#3]

@NikolinoDE thank you so much for this i'll test this and keep you in touch

[#4]

@NikolinoDE Thank you sooo much it's working !

i would like to ask you for a last favor please i would like to do the following :

- If an ID exists in Sheet 2 but not in Sheet 1 then remove the row ID from Sheet 2, could you please help me for that ?

[#5]

@MicrosoftNewbie121 

You can modify the existing VBA code to check if an ID exists in Sheet2 but not in Sheet1, and if so, delete the corresponding row in Sheet2. Here's the updated code:

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
    
    ' Check for IDs in Sheet2 that don't exist in Sheet1 and delete the corresponding rows
    For Each idCell In idCol2
        If IsError(Application.Match(idCell.Value, idCol1, 0)) Then
            idCell.EntireRow.Delete
        End If
    Next idCell
    
    MsgBox "Data updated successfully!", vbInformation
End Sub

This code introduces a new loop after updating or creating rows in Sheet2. It checks for IDs in Sheet2 that do not exist in Sheet1 and deletes the corresponding rows in Sheet2. Please replace the existing code in your VBA editor with this updated code and test it in your example file.

If you prefer a solution without VBA, you can use Excel formulas and a combination of filtering and copying. Here are the steps:

1. Identify IDs in Sheet2 not in Sheet1:
• In Sheet2, add a helper column (let's say in column E) with the formula:

=IF(ISERROR(MATCH(B2, Sheet1!B:B, 0)), "Delete", "")

• Drag this formula down for all rows.
1. Filter Sheet2 based on Helper Column:
• Apply a filter to the data in Sheet2 based on the values in column E.
• Filter for "Delete" in column E.
2. Delete Filtered Rows:
• Select the visible cells (excluding the header) in Sheet2.
• Right-click on the selection, choose "Delete," and select "Entire Row."
3. Remove Helper Column:
• You can now remove the helper column (column E) in Sheet2.

This approach uses Excel formulas and features without VBA but involves manual steps like filtering and deleting. It relies on the helper column to identify rows in Sheet2 that don't exist in Sheet1.

Remember to adapt the column references based on your actual data structure. If your IDs are in a different column or your data starts from a different row, adjust the formulas accordingly.

Please test this approach on a copy of your data to ensure it meets your requirements.

[#6]

@NikolinoDE 

Hello back thanks alot for the answer

when i tested the new code (after deleting the ancient one i get this error) can you please help me ? error 1004 : delete method of Range class failed.

[#7]

@MicrosoftNewbie121 

The "Run-time error 1004" typically occurs when there's an issue with attempting to delete a row using the Delete method, such as when the row is part of a table or contains merged cells. To handle this situation, you can use a different approach to delete the row. Here's an updated version of the code that should help avoid this error:

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
    
    ' Check for IDs in Sheet2 that don't exist in Sheet1 and delete the corresponding rows
    Dim deleteRows As Collection
    Set deleteRows = New Collection
    
    For Each idCell In idCol2
        If IsError(Application.Match(idCell.Value, idCol1, 0)) Then
            ' Add the row number to the collection for deletion
            deleteRows.Add idCell.Row
        End If
    Next idCell
    
    ' Delete rows from the bottom up to avoid issues with row deletion
    For i = deleteRows.Count To 1 Step -1
        ws2.Rows(deleteRows(i)).Delete
    Next i
    
    MsgBox "Data updated successfully!", vbInformation
End Sub

This code introduces a Collection called deleteRows to store the row numbers that need to be deleted. It then deletes the rows from the bottom up to avoid issues with row deletion. Please replace the existing code in your VBA editor with this updated code and test it again. Let me know if it resolves the issue.The text and vba code were edited with the help of AI.

[#8]

Hello NikolinoDE
i still have the same error but this time it's deleting the rows but i still have same error,
any idea how to overcome that ?

[#9]

Can you share your workbooks for testing?