Comparing two columns in two different open worksheets

[Suryaprakash]

I have two worksheets, of which Namelist.xls is a master file and the other one is named Namelistanddata.xls.​

Namelist.xls has 3 columns. Column B is titled "names" and there are 300 names. Namelistanddata.xls has columns A:P, with column D titled "names" having 5000 "names" and these 5000 names also contain the above 300 names. I wish to compare column B of namelist.xls with column C of Namelistanddata.xls and wherever the names are matching in the file Namelistanddata, I wish to copy those complete rows (A:P) in a new file. So, in effect, I should get a new file with 300 rowsof data.​

I am using excel 2000 and my files include only one sheet each. Could anyone help me out with a macro for this job, as manually matching through 5000 names is just impossible and I am required to do this process everyday with new set of names in the second file.​

 

[Comfy]

I've never used Excel 2000 but would using an Advanced Filter be an option?

Filter the mastersheet using the 300 names and copy the visible results to a new sheet?

 

[Suryaprakash]

Dear Comfy:

The method suggested by you would be very tedious and time consuming. I believe there could be a much simpler solution, like a macro.

Thanks for your suggestion