update if matched found otherwise keep unchanged

  • Hi Everone

    Hope all are well.


    I search many forums to find something but nothing seems to be similar to the problem which I describe below, hope someone helps me with a solution.


    I have two worksheets named 1 and 2, Sheet 2 is the source data sheet and sheet 1 is the destination datasheet.


    In sheet 1


    SLIDName---Contact PersonClassResult
    1111MartinJustinO156
    2112JhonRubelA160
    3113AlbertRoseC190


    And In Sheet 2


    IDContact PersonClassResult
    112RubelA180
    113RoseC190


    Now the question is after putting data in sheet 2, A command button will be clicked then the row checked in sheet 1 to the last value for a matching (ID, Contact Person & Class) if any row matched with these three conditions (ID, Contact Person & Class) found then the matched Result Column data update and unmatched data will be same as it is.


    Like in sheet 1 & sheet 2 ID 112 & 113 matched, but only SL 2 result value will be updated as (ID, Contact Person & Class) matched and the result value is different but

    SL 3 will be unchanged as it is the same as before. and

    SL1 will remain the same too.


    Hope someone helps me to ease the difficulties of value update.


    Thanks in Advance

  • Thanks, RoyUK for updating the title of the thread.


    I have attached a Sample.xlsx File.


    The color yellow is used to show the matching cell.


    Sorry, If I am unable to describe my thoughts to you.


    Thanks and always appreciate your help.

  • Try the attached.


    Code assigned to the button

  • Thank You KjBox,


    Your code works fine but when I click to update it removes all the formulas in Sheet1 or Tabel1 also It changes other cell values.


    Suppose I have a particular row. The code changes all the values of that particular row or it duplicates some other row.


    I here attached my work file for your kind consideration. For Upload.xlsm


    Thanks and best regards

  • You did not have any formulas in your sample workbook, so was not really an accurate sample! I will look at your actual file asap

    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.

  • You have "METRO" in the Update Table and "Metro" in the Network Listing Table. Do you want the matching to be case sensitive or not?

    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.

  • Yes, Kjbox my sample file has no formula. Sorry for that As I thought VBA will not affect the formula.


    As it affects the formula so my search function is not working.


    Thank you for the feedback.

  • You have "METRO" in the Update Table and "Metro" in the Network Listing Table. Do you want the matching to be case sensitive or not?

    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.

  • In that case change the code to this, note the Monthly Rent will not update because of the "METRO", change that to "Metro" and update is done.


    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 have attached the file again.

    In this, in the Network listing, you see 16048 two Entries. Then in the update sheet, you see the data I wanted to update.

    Now if I click on the update then something happens that is not clear to me.


    I do not understand the cause. Please see what is happening?


    Thanks and Best regards.

  • From the previous post.


    The Frist time it updates.

    Then I changed the monthly rent in the update sheet of the same entry and update

    then 16048 is not found in the network listing also not found by the search.


    Hope you understand, sorry for the poor description.


    Thanks and best regards

  • The crazy filtering method you are using interferes with the Update code. Why are you using that and not the built-in Table filters?


    Also the Branch Codes are formatted as Text or preceded by an apostrophe


    Attached is your file with various codes modified and the new Update code working.

  • Dear KjBox,


    I am really impressed by the work you did for me. It works like a magic.


    "Sometimes giving thanks is very little as appreciation. "

    May God grant you a long and happy life.


    Thanks and Best Regards

  • Thank you, and you're welcome.

    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!