Compare Names between two files and get a address

[nt_beans]

Hello,
I've two excel files (A and B ). File A got Names of 1000 employees of our firm. File B is complete list and got names of 35000 employees of our firm including their location and country.
A macro should compare names between File A and B and populate Location in File A after looking up in File B

Example:
File A
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]Andrew Corey[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Michael Wilson[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

File B

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location[/TD]
[TD]Country[/TD]
[/TR]
[TR]
[TD]Richard Spicer[/TD]
[TD]New York[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]Michael Wilson[/TD]
[TD]Chicago[/TD]
[TD]USA[/TD]
[/TR]
[TR]
[TD]Rajesh Sharma[/TD]
[TD]Barcelona[/TD]
[TD]Spain[/TD]
[/TR]
[TR]
[TD]Andew Corey[/TD]
[TD]Seattle[/TD]
[TD]USA[/TD]
[/TR]
</tbody>[/TABLE]

Desire OutPut
A macro should compare names between File A and B and populate Location in File A after looking up in File B

File A
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Location[/TD]
[/TR]
[TR]
[TD]Andrew Corey[/TD]
[TD]Seattle[/TD]
[/TR]
[TR]
[TD]Michael Wilson[/TD]
[TD]Chicago[/TD]
[/TR]
</tbody>[/TABLE]


Hope someone can assist with my issue.

thanks

 

[alansidman]

Use the Vlookup function to do this. For syntax on Vlookup, look at this link.

https://www.techonthenet.com/excel/formulas/vlookup.php

 

[Fluff]

If you want a macro try

Sub GetLocation()

   Dim Cl As Range
   Dim SrcSht As Worksheet
   Dim DestSht As Worksheet
   
   Set DestSht = Sheets("[COLOR=#ff0000]New[/COLOR]")
   Set SrcSht = Workbooks("[COLOR=#ff0000]Book1.xlsm[/COLOR]").Sheets("[COLOR=#ff0000]JCX[/COLOR]")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In SrcSht.Range("A2", SrcSht.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In DestSht.Range("A2", DestSht.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 1).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

This looks in Cols A & B for the data.
Change the sheet & workbook names in red to suit.
The code needs to go in the Destination workbook & both books need to be open

 

[codeliftsleep]

I'd put them in a variant array and then search the array for the value it will be significantly faster.

 

[Fluff]

@codeliftsleep
Rather than telling people what you would or wouldn't do, why not show us what you'd do (ie provide the code).

 

[codeliftsleep]

@codeliftsleep
Rather than telling people what you would or wouldn't do, why not show us what you'd do (ie provide the code).

Normally I do, but I am on a smartphone right now and don't have access to excel. I always test the code out before I post it to eliminate mistakes.

When I get home I'll write something up and post it

 

[Fluff]

I look forward to it.

 

[nt_beans]

Works like a charm..thank you so much Fluff

 

[Fluff]

Glad to help & thanks for the feedback

 

[codeliftsleep]

If you want a macro try

Sub GetLocation()

   Dim Cl As Range
   Dim SrcSht As Worksheet
   Dim DestSht As Worksheet
   
   Set DestSht = Sheets("[COLOR=#ff0000]New[/COLOR]")
   Set SrcSht = Workbooks("[COLOR=#ff0000]Book1.xlsm[/COLOR]").Sheets("[COLOR=#ff0000]JCX[/COLOR]")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In SrcSht.Range("A2", SrcSht.Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In DestSht.Range("A2", DestSht.Range("A" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then Cl.Offset(, 1).Value = .Item(Cl.Value)
      Next Cl
   End With
End Sub

This looks in Cols A & B for the data.
Change the sheet & workbook names in red to suit.
The code needs to go in the Destination workbook & both books need to be open

Dictionary is a great solution as well allowing key, value pairs. Was not even aware you could use dictionaries in VBA...only thing that could cause an issue if two people have the same name because you cant have a duplicate key with a dictionary.

This brings up some interesting possibilities for me with some things I am doing...

I'm getting ready to post this with a variant array but I doubt there would be much if any difference in speed versus a dictionary.

OP, how would you handle if someone had the exact same name in the company? There would need to be a way of differentiating which person you were wanting...