Macro to match and return data

[lalableah]

Hi everyone,

I'm trying to match the data in a column from one excel file with another column of data from a separate excel file. If there is a positive match, I would like it to return a cell data on the same row in the following column on the first excel file. I understand that this can be done with vlookup but because the data in separate excel file contains commas and thus I can't get a true match.

To visualize what i'm trying to illustrate, i'll breakdown into steps.

I would like to match the data below in excel file 1 to excel file 2.

Excel file 1
83883
89342
83603
84445
434961

Excel file 2
83883
89342, 541879
83603
522354, 84445
83985, 434961

Lets imagine that there is another column of data next to the list of data in excel file 2. If there is a positive match between excel file 1 to excel file 2, i would like to return cell next to the data in excel file 2 on the same row.. to the cell next to the data in excel file 1.

This is a pain for me as I have a column with few thousands of such data.

Please help me out with a macro for this task.

Thanks

 

[lalableah]

bumpppppppppppppppppp

 

[jecruz]

are the data in excel file 1 unique? same question for excel file 2.

 

[lalableah]

are the data in excel file 1 unique? same question for excel file 2.

Hi,

Yes the data in excel file 1 and 2 are unique. as such, in one column, there is no duplicate data.

 

[jecruz]

oh. and are the data in file 1 sure to exist in file 2?

 

[lalableah]

oh. and are the data in file 1 sure to exist in file 2?

Hi,

Nope. There might be data in file 1 that doesnt exist in file 2. As such, if the macro can return a "NEW" to indicate that it doesnt exist in file 2 ... that would be good.

Thanks!

 

[lalableah]

anyone able to help me out?

 

[lalableah]

bumppppppppppp

 

[lalableah]

bumppppppppppp

 

[lalableah]

bumpppppppppppppp