Macro to match and return data

lalableah

New Member
Joined
Jun 22, 2011
Messages
15
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

jecruz

New Member
Joined
Sep 27, 2011
Messages
9
are the data in excel file 1 unique? same question for excel file 2.
 

jecruz

New Member
Joined
Sep 27, 2011
Messages
9

ADVERTISEMENT

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

lalableah

New Member
Joined
Jun 22, 2011
Messages
15
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

New Member
Joined
Jun 22, 2011
Messages
15

ADVERTISEMENT

anyone able to help me out?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,742
Messages
5,638,100
Members
417,007
Latest member
joellange20

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top