VB script for Comparing 3 columns against 3 other columns in two worksheets

  • I have a an excel 2016 work book that has two worksheets in it they both have three columns in them and i am trying compare the data in "sheet 1" to the data in "sheet 2" to see if there are changes in them. so for my sheet 1 i have last name in column b and first name in column c and location in column d and i need to compare the first and last name and location to sheet 2 witch has last name in column a and first name in column b and location in column c and i need to know if the location is different between the two. and i have been searching this on the internet for three days now and have not came across anything that works. I Do apologize but i know nothing about how to do this in excel. So if i sound dumb then i apologize and will take down this post.

  • It would be easier to help and test possible solutions if you could attach a copy of your file.

    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.

  • Hello and Welcome to the Forum :)


    Mumps is 100% right ...!!!


    Truly impossible to suggest a generic solution for your specific problem ...


    Please attach a sample file ...


    Make sure the structure is identical to your ' real life ' workbook ... and, for confidentiality purposes ... just remove all the Names ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

    Edited once, last by Carim ().

  • I noticed that in Sheet2 you some data starting in row 376 that doesn't include any personal names in columns A and B and no corresponding location in column C. Do you want this data ignored? There is one exception to this in row 379 which does have a last and first name (Jones, Don) but no corresponding location. How do you want to handle this situation? Also, what do you want to do if a different location is found? Do you want to highlight it or copy it back to Sheet1? Please clarify in detail.

    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.

  • For the first question yes i would like that data ignored and for the second question yes i would like to have the ones that the locations are different if i could get them highlighted that would be great. Thank you for your help with this.

  • I have found a few more problems with the data. The first names in Sheet1 have a leading blank space and initials while the first names in Sheet2 do not. Some locations in Sheet1 don't match the locations in Sheet2. For example, Sheet1 has "Perrysburg- Commerce" as a location while Sheet2 has "Commerce" as a location or "Wauseon Outpatient" and "Wauseon" . These problems make it is difficult to find matches. Please advise.

    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.

  • Try the attached file. I still had to make some corrections to your data. "Napoleon" was spelled wrong in one sheet and "Bryan" had a trailing space in one sheet. You have to be very careful that all the data matches exactly otherwise the macro will not return the expected correct results.

    Files

    • dheckart.xlsm

      (33.69 kB, downloaded 43 times, last: )

    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.

  • Thank you very much i do Appreciate it. so maybe you can get me started writing a script that would compare the two sheets. I am not an excel person or programmer at all and i really do not even know where to start. And again thank you for all the help with this or maybe give me some ideas of were to start any help would be greatly appreciated.

  • so maybe you can get me started writing a script that would compare the two sheets

    The macro I suggested already compares the two sheets. Is this request different from the one in your original post? Please clarify.

    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.

  • Are you saying that the file I posted in Post #10 had no macro in it? I just downloaded the file and everything was in working order with the macro in Module1.

    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.

  • no Mumps it was my fault. I guess i did not know how to run the macros but i figured it out and it ran and worked perfectly for me again i am sorry for the ignorance on my part. And a huge thank you for your help with this.

  • As Usual ... Mumps does deserve the GOLD MEDAL ... for his kindness ( and his Unlimited Patience... ) :)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • You are most welcome. :) You can mark the thread as solve by clicking the "Settings" button.


    Carim

    Thanks once again for the kind words. ;)

    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.