search a file for text from another file

[cadman33619]

Hi all,

i have a file with job names (about 150) and i need to know if they appear in a 2nd file. they are both large files with multiple sheets. I can use the find option and select all sheets but i would still have to type (or copy/paste) each job name from file 1 into the search field of file 2. that will take forever.

is there a way to compare the two files to see if the job names are in file 2?
or perhaps another parameter..like site address?

thanks

cadman

[tigeravatar]

cadman33619,

Welcome to the forum.

Before going into the macro, let me define a couple things:
wbDest: short for Destination Workbook. This is the workbook that contains the list of you want to find in a different workbook
wbData: short for Data Workbook. This is the workbook that will be searched for the list of items.

You did not specify where the list of items in wbDest would be located, so the macro assumes that they're in column A starting at row 1 of the activesheet. You can change the column letter and startrow number in the macro as necessary.
You also did not specify what you want to happen once a match has been found. Do you want to return an adjacent cell's value to wbDest? Do you want to delete the match in wbDest or wbData? Do you want to highlight the match in both workbooks (or just one)? Without knowing what to do once a match was found, I simply have the macro record the sheetname and cell address of the match and display the data to you in a message box at the end of the routine.

That should get you started. Here's the macro:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar