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

Brass Contributor

Hello, can you please help me ?

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

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

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

- Loop this until last row

(Both workbooks use same collum name for the same items)
Thank you forward for the help ^^

2 Replies


It seems like you're describing a task related to working with data in two different workbooks (wb1 and wb2). If you want to find a specific ID in workbook 1 (wb1) and replace the values in columns A, B, C, and D with the corresponding values from workbook 2 (wb2), you can follow these steps using Microsoft Excel:

  1. Open both Workbooks:

    • Open both wb1 and wb2 in Microsoft Excel.
  2. Identify the ID in Workbook 1:

    • In wb1, locate the specific ID you want to find. Note the row number or cell where this ID is present.
  3. Switch to Workbook 2:

    • Go to wb2 where the values you want to replace are located.
  4. Identify the Corresponding Values:

    • In wb2, find the row or cell that contains the same ID you identified in wb1. Note the values in columns A, B, C, and D that you want to replace in wb1.
  5. Return to Workbook 1:

    • Go back to wb1.
  6. Replace Values:

    • Select the row or cell in wb1 where the ID is located.
    • Manually replace the values in columns A, B, C, and D with the corresponding values from wb2.


    • Use Excel formulas or VLOOKUP to automate the process. For example, if the ID is in cell E1 in wb1, and the values in wb2 are in columns A, B, C, and D, you can use formulas like:
      excelCopy code
      =VLOOKUP(E1, [wb2.xlsx]Sheet1!$A$1:$D$100, 1, FALSE) ' For column A =VLOOKUP(E1, [wb2.xlsx]Sheet1!$A$1:$D$100, 2, FALSE) ' For column B =VLOOKUP(E1, [wb2.xlsx]Sheet1!$A$1:$D$100, 3, FALSE) ' For column C =VLOOKUP(E1, [wb2.xlsx]Sheet1!$A$1:$D$100, 4, FALSE) ' For column D

    Note: Adjust the range and sheet name ([wb2.xlsx]Sheet1) based on your actual workbook and sheet names.

  7. Update Workbook:

    • Save your changes in wb1.

Remember to backup your workbooks before making significant changes, and carefully verify the IDs to ensure accurate replacements.

@Tomsmith1122 hello Tomsmith thank you so much but I would like to do this in Macro for leaning purposes I forgot to say it in the post sorry :(