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

 

[Fluff]

@nt_beans
codeliftsleep makes good point about duplicate names.
If this is a potential problem, let us know.

 

[codeliftsleep]

Public sub MatchRecords()
  Dim S1,S2 as worksheet
  Dim lookupArr(), sourceArr() as variant
  Dim i, j, srcRows, lookRows as long
  Dim srcRng as Range
  Dim lookRng as Range

  set S1= Sheets("SourceSheet")
  set S2= Sheets("LookupSheet")

  srcRows= 
  S1.Range("A:A").Find(What:="*", searchDirection:=xlPrevious, LookIn:=xlValues).Row
  S2.Range("A:A").Find(What:="*", searchDirection:=xlPrevious, LookIn:=xlValues).Row

  set srcRng= S1.Range("A1:B" & srcRows)
  set lookRng= S2.Range("A1:B" & lookRows)

  sourceArr= srcRng
  lookArr= lookRng

  set srcRng= Nothing
  set lookRng= Nothing

  'Loop through the arrays---can't use filter on jagged arrays in VBA
  'Binary search algorithm would be much faster but probably overkill

  For i = LBound(lookupArr,1) to UBound(lookupArr, 1)
    For j = LBound(sourceArr,1) to UBound(sourceArr,1)
      If lookupArr(i,1) = sourceArr(j,1) then
         lookupArr(i,2)= sourceArr(j,2)
         Exit For
      End if
     Next j
    Next I
  S2.Range("A1:B"&lookRows).Value= lookupArr
End sub

 

[RoryA]

Just FYI, this:

Dim S1,S2 as worksheet

should be:

Dim S1 as worksheet,S2 as worksheet

 

[codeliftsleep]

Just FYI, this:

Dim S1,S2 as worksheet

should be:

Dim S1 as worksheet,S2 as worksheet

Why? Its perfectly legal to declare variables like this

 

[Fluff]

You are only declaring S2 as a worksheet, S1 is being declared as variant.
The same goes for this

Dim i, j, srcRows, lookRows as long

i, j & srcRows are all variant & only lookRows is long.

Also, neither srcRows nor lookRows has any value, & you haven't declared
lookArr

 

[codeliftsleep]

You are only declaring S2 as a worksheet, S1 is being declared as variant.
The same goes for this

Dim i, j, srcRows, lookRows as long

i, j & srcRows are all variant & only lookRows is long.

Also, neither srcRows nor lookRows has any value, & you haven't declared
lookArr

More reason why VBA is considered a toy by programmers...those are all legal declarations in most programming languages.

 

[RoryA]

Yes, there is a certain type of programmer that thinks that anything that doesn't work the way they think it should is stupid or trivial. Presumably they felt the same way about Visual Basic 3/4/5/6 which were remarkably successful nonetheless.

 

[codeliftsleep]

Yes, there is a certain type of programmer that thinks that anything that doesn't work the way they think it should is stupid or trivial. Presumably they felt the same way about Visual Basic 3/4/5/6 which were remarkably successful nonetheless.

Apparently Microsoft agreed with me as this IS legal in VB.net. And pretty much every other programming language I work in outside VBA.

VBA is pretty cool and far more useful than I thought when I started working with it. I thought it was a matchbox car but it ended up being one of those cool electric cars you are able to drive around in as a kid. A high end toy, but a toy nonetheless at the end of the day. Just too many things you can't do in it that you should be able to.

And versions of VB prior to VB.net left a lot to be desired.

 

[RoryA]

All languages leave something to be desired - that's why they evolve. Their evolution does not in any way make what went before useless or trivial. VB was a good and productive language, as is VBA (since it's based on it). The sheer number of man hours saved by its application across its enormous user base makes it far from a toy in my opinion. It is extremely unfortunate that Microsoft doesn't value it more highly and has chosen not to develop it, given that it has been one of the killer features of Office for many years.

 

[codeliftsleep]

All languages leave something to be desired - that's why they evolve. Their evolution does not in any way make what went before useless or trivial. VB was a good and productive language, as is VBA (since it's based on it). The sheer number of man hours saved by its application across its enormous user base makes it far from a toy in my opinion. It is extremely unfortunate that Microsoft doesn't value it more highly and has chosen not to develop it, given that it has been one of the killer features of Office for many years.

The difference is the other languages have been fully developed or are continuously being improved and having new versions released.

VBA is neither...its "unfinished"....Abandoned by Microsoft to focus on bigger and better things. When was the last meaningful VBA release?