Auto fill info from one file to another

**mona111** · 11-14-2014, 04:34 PM

I have two excel files. The only thing in common between those files is the ExpertID, which is a unique identifier for researchers. Master.xlsx has the ExpertName and ExpertID, DukeResearchers.xlsx only has ExpertID. I need to transfer ExpertNames to DukeResearchers.xlsx The only problem is DukeResearchers.xlsx has more than 85,000 rows of information, and it lists each ExpertID multiple times (as opposed to only once in Master.xlsx).

For example, Master.xlsx says that John Smith’s ExpertID is 0030, Jane Doe’s ExpertID is 0031, Robert Johnson’s ExpertID is 0032, and so on. DukeResearchers.xlsx will list 0030 thirty times, 0031 a hundred times, and 0032 twenty-five times. The number of times an ExpertID is listed depends on how many publications the expert has, so 0030 has thirty publications. I need to fill in the name John Smith for all 0030, Jane Doe for all 0031, Robert Johnson for all 0032…..except over 85,000 times. Is there a formula that will allow me to do this?

**p24leclerc** · 11-14-2014, 06:35 PM

Welcome to this forum, I hope you'll find all the help you need here.
Here is a formula that you would put in DukeResearchers.xlsx workbook.
I assumed the expertID was in column A and the ExpertName in column B in the Master.xlsx workbook. If not, you can change the reference in the formula.
I also assumed the ExpertID was in column A in the DukeResearchers.xlsx workbook. Place the formula in any column you want in the workbook.

Please Login or Register  to view this content.

I must warn you that 85000 formulas like this will slow you Excel application unless you have a really fast computer.
The nice thing about Excel and this formula is that it is not necessary to have the Master file open to update the values in the DukeReseachers workbook. You'll be prompted to update them when you will open it.
Hope this helps

**FDibbins** · 11-14-2014, 07:12 PM

Agree that this formula could tend to slow things down, but I suggest you avoid using entire column ranges, and restrictthe range to what you need.

Try this instead...

=VLOOKUP(A2,[Master.xlsx]Sheet1!$A$1:$B$100000,2,FALSE)
this will reduce the range to about 1% compared to a column range