If WS 2 ID exists in WS 1, update WS 1 column value, else create new row with WS 2 data

  • In WS 2, I import data, including a key value in column D. In WS 1, I have a master list of data, including a key value in column D. I am trying to create a macro that does this:


    For each key value in WS 2, column D:

    • If key value exists in WS 1, column D
      • Update columns E and F in row with matching ID
    • If key value does not exist in WS 1, column D
      • Add columns D through M for non-matching key to the next empty row in WS 1



    I've tried to cobble together a macro based on snippets on this site, but am stuck on adding a new row. Any help is appreciated.

  • Please attach a sample workbook with a separate sheet that shows the desired result.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks, KjBox. I hope that the same below will suffice.


    1) WS1 (before running macro)

    ABCDEFGHIJKLM
    A1
    K1abc01
    K2def02
    A2


    2) WS2 (after importing data)

    ABCDEFGHIJKLM
    K1
    pqr03
    K2stu04
    K3abc01pqrtrue05stufalsevwxtrue


    3) WS1 (after running macro requested in this post)

    ABCDEFGHIJKLM
    A1
    K1pqr03
    K2stu04
    A2
    K3abc01pqrtrue05stufalsevwxtrue


    K1 and K2 exist in column D of WS1, so their columns E and F values are updated to the values for K1 and K2 in WS2

    K3 does not exist in column D of WS1, so columns D through M from WS2 for K3 are copied into the first empty row in WS1

  • Try:

    Change the sheet names to suit your needs.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • This is an alternative method which will be faster if your data set is large. Assumes Row 1 in each sheet is a Header Row.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!