copy two Columns into one and remove duplicates

i have two sets of data, both have a Column called Job number. In the second list there may be multiply instances of the job number. Also in the second instance of data there may be Job numbers that do not appear on the first list.

My aim is to merge all the data from both lists into one as automatically as possible. Both lists are updates daily.

My Line of thinking is to get the one Job number list and use Vlookup or sum product or sum if to do the rest

to get the Job numbers as one list i am trying trying to use the following VBA called from a button click

Please Login or Register  to view this content.

any ideas why it wont work??

thanks

Do you have an example workbook?

cant remember the old code error but with the above i get this error

subscript out of range

here is the excel document

Job summary template.xlsm

thanks for looking

old error with original code is method range of object _worksheet failed

hope that helps

i don't want to over write data but append to the bottom before dedicating it

i have managed to get a code working for removing duplicates from the table column

Please Login or Register  to view this content.

now i just need a code to add to columns together under one the run the above

any ideas?

tweaked a little as second list job number is in column B

but this works perfectly

many thanks

Please Login or Register  to view this content.