Compare 2 Tables Of X Columns

  • Hi all,


    I have data in sheet1 and sheet2.


    I would like a macro that will compare the concatenated values of columns F, G and X of sheet1 vs the concatenated values of columns U, V and W of sheet2.


    Those in sheet1 that can not be found in sheet2 will then be shown in a msgbox. If all are found, the msgbox will simply say "Nothing to show".


    Thanks

  • Re: Compare 2 Tables Of X Columns


    Hi Patsy


    The following code should do what you want:



    The code uses the last row in column F as the row count. I attach an example for you.


    There may be more elegant replies to your question, but it works ;)


    HTH
    Jamie

  • Re: Compare 2 Tables Of X Columns


    Hi Jamie,


    Looks great, although would it be opssible it the msgbox shows all simultaenously instead of one item at a time?


    Thanks

  • Re: Compare 2 Tables Of X Columns


    Hi Patsy


    try


  • Re: Compare 2 Tables Of X Columns


    Hi Jamie,


    Looking better. However I probably should have mentioned that the list in sheet1 are repeating. So there could be 20 or so lines of the same combinations.


    With above case, wwhen I run your code, it shows up the missing combination in as many times as they occur in sheet - it should really just show 1.


    Is that possible?


    Thanks

  • Re: Compare 2 Tables Of X Columns


    Hi Patsys


    added a find function to check if missing already exists. Should remove duplicates:


  • Re: Compare 2 Tables Of X Columns


    Sorry Patsy


    just noticed a mistake in the code - I was missing the last line.
    Plus, it was using the 1st row. Does your data have headers?
    If so, this code is correct:




    if you dont have headers and want to compare the first line, this should be OK



    Jamie

Participate now!

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