Compare two spreadsheets and populate address/city/state/country

[nt_beans]

Hi All,

Need a help in capturing data from another spreadsheet. I have two files
1. File A (1200 rows)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Exchange[/TD]
[TD]City[/TD]
[TD]Address1[/TD]
[TD]Address2[/TD]
[TD]State[/TD]
[TD]Country[/TD]
[TD]Postal[/TD]
[/TR]
[TR]
[TD]Abe John[/TD]
[TD]CBOT[/TD]
[TD]New York[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mark Twain[/TD]
[TD]CME[/TD]
[TD]Singapore[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

2. File B ( 300 rows)
[TABLE="width: 500"]
<tbody>[TR]
[TD]Address1[/TD]
[TD]Address2[/TD]
[TD]City[/TD]
[TD]State[/TD]
[TD]Country[/TD]
[TD]Postal[/TD]
[/TR]
[TR]
[TD]320 W, 42nd st[/TD]
[TD][/TD]
[TD]New York[/TD]
[TD]NY[/TD]
[TD]US[/TD]
[TD]10048[/TD]
[/TR]
[TR]
[TD]140 Governers Lane[/TD]
[TD][/TD]
[TD]Miami[/TD]
[TD]FL[/TD]
[TD]US[/TD]
[TD]40323[/TD]
[/TR]
[TR]
[TD]34, financial district[/TD]
[TD]zone2[/TD]
[TD]Singapore[/TD]
[TD][/TD]
[TD]SG[/TD]
[TD]324A4[/TD]
[/TR]
</tbody>[/TABLE]

Output
I want to compare file A and B by "City" and populate address1/address2/State/Country/Postal in File A

[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Exchange[/TD]
[TD]City[/TD]
[TD]Address1[/TD]
[TD]Address2[/TD]
[TD]State[/TD]
[TD]Country[/TD]
[TD]Postal[/TD]
[/TR]
[TR]
[TD]Abe John[/TD]
[TD]CBOT[/TD]
[TD]New York[/TD]
[TD]320 W, 42nd St[/TD]
[TD][/TD]
[TD]NY[/TD]
[TD]US[/TD]
[TD]10048[/TD]
[/TR]
[TR]
[TD]Mark Twain[/TD]
[TD]CME[/TD]
[TD]Singapore[/TD]
[TD]34, financial district[/TD]
[TD]zone2[/TD]
[TD][/TD]
[TD]SG[/TD]
[TD]324A4[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance and please let me know if you have any further questions.

 

[István Hirsch]

Are there duplicate cities in File B?

 

[nt_beans]

No duplicates

 

[nt_beans]

any help will be greatly appreciated.

 

[Fluff]

How about

Sub GetAddress()

   Dim Cl As Range
   Dim CityWs As Worksheet
   Dim ExWs As Worksheet
   
   Set CityWs = Workbooks([COLOR=#ff0000]"FileB.xlsx[/COLOR]").Sheets("[COLOR=#ff0000]Cities[/COLOR]")
   Set ExWs = ThisWorkbook.Sheets("[COLOR=#ff0000]Sheet1[/COLOR]")
   
   With CreateObject("scripting.dictionary")
      For Each Cl In CityWs.Range("C2", CityWs.Range("C" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then .Add Cl.Value, Cl
      Next Cl
      For Each Cl In ExWs.Range("C2", ExWs.Range("C" & Rows.Count).End(xlUp))
         If .exists(Cl.Value) Then
            Cl.Offset(, 1).Resize(, 2).Value = .Item(Cl.Value).Offset(, -2).Resize(, 2).Value
            Cl.Offset(, 3).Resize(, 3).Value = .Item(Cl.Value).Offset(, 1).Resize(, 3).Value
         End If
      Next Cl
   End With
   
End Sub

This needs to go in file A, with file B open when you run it.
Change values in red to suit