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)

Name	Exchange	City	Address1	Address2	State	Country	Postal
Abe John	CBOT	New York
Mark Twain	CME	Singapore

<tbody>
</tbody>

2. File B ( 300 rows)

Address1	Address2	City	State	Country	Postal
320 W, 42nd st		New York	NY	US	10048
140 Governers Lane		Miami	FL	US	40323
34, financial district	zone2	Singapore		SG	324A4

<tbody>
</tbody>

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

Name	Exchange	City	Address1	Address2	State	Country	Postal
Abe John	CBOT	New York	320 W, 42nd St		NY	US	10048
Mark Twain	CME	Singapore	34, financial district	zone2		SG	324A4

<tbody>
</tbody>

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