asked on

Excel: Update 1 Spreadsheet with data from another

Hi

I have an excel spreadsheet File1 containing a blank Country Codes column (A) and Country names column (B) + other data Several Thousand rows so the same country name will appear 0, 1 or multiple times

I have a second excel spreadsheet File2 containing both Country Codes column (A) and Country names column (B)

is it possible to loop through the all the rows of File1 using the value, Country name, in column (B) to search File2>column (B) return either column (A) value or Not Found and update column (A) in File1?

If it's easier I can copy the values from File2 temporally into File1 or create a VBA hash (if there is a thing)

Sample bellow with desired output

File1

Code	Name	Foo	Bar
	Argentina	other stuff 1	other stuff 2
	Argentina	other stuff 1	other stuff 2
	Aruba	other stuff 1	other stuff 2
	Argentina	other stuff 1	other stuff 2
	Australia	other stuff 1	other stuff 2

Open in new window

File2

Code	Name		
AR	Argentina		
AM	Armenia		
AA	Aruba		
AT	Ashmore and Cartier Islands		
AS	Australia		
AU	Austria		
AJ	Azerbaijan

Open in new window

File1 Output

Code	Name	Foo	Bar
AR	Argentina	other stuff 1	other stuff 2
AR	Argentina	other stuff 1	other stuff 2
AA	Aruba	other stuff 1	other stuff 2
AR	Argentina	other stuff 1	other stuff 2
AS	Australia	other stuff 1	other stuff 2

Open in new window

ASKER CERTIFIED SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Shums Faruk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

trevor1940

ASKER

Hi mate

This might take a bit longer to short out

I'm getting "the formula you typed contains an error" excel highlights '$A,MATCH'

At this stage unsure if typo between systems or if some kind of file coruption

in another cell I've typed =1+1 and I'm not seeing 2

Unfortunately i can't download your VBA solution I think firewall is blocking the Macro I'll try at home

Shums Faruk

Hi Trevor,

If you have both workbook opened then try below in A2 in File1 and drag down:

=IFERROR(INDEX(File2.xlsx!$A:$A,MATCH($B2,File2.xlsx!$B:$B,0)),"Not Found")

Open in new window

trevor1940

ASKER

I'm getting your examples transferred between systems in order to workout why I'm getting the error

In the mean time I've been looking at our VBA solution I've added a few comments to aid my understanding could you confirm this?

Sub UpdateCode()
Dim SrcWB As Workbook, TrgWB As Workbook
Dim SrcWS As Worksheet, TrgWS As Worksheet
Dim SrcLR As Long, TrgLR As Long, i As Long, a As Long
Dim SrcFile As String
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFilePicker)
    .Title = "Select Source File"
    .AllowMultiSelect = False
    .Filters.Clear
    .Filters.Add "Source File", "*.xl*"
    If .Show = -1 Then
        SrcFile = .SelectedItems(1)
    Else
        MsgBox "You didn't select any Source File!", vbExclamation
        Exit Sub
    End If
End With
Workbooks.Open (SrcFile)
Set SrcWB = ActiveWorkbook
Set SrcWS = SrcWB.Sheets(1) // This is in File2
Set TrgWB = ThisWorkbook
Set TrgWS = TrgWB.Sheets(1) // File 1 Cuurent  file
SrcLR = SrcWS.Range("A" & Rows.Count).End(xlUp).Row
TrgLR = TrgWS.Range("B" & Rows.Count).End(xlUp).Row // This is the colmn containing  the Country Name
For i = 2 To SrcLR
    For a = 2 To TrgLR
        If SrcWS.Cells(i, 2) = TrgWS.Cells(a, 2) Then
            TrgWS.Cells(a, 1).Value = SrcWS.Cells(i, 1).Value // This is the Target column 
        End If
        If IsEmpty(TrgWS.Cells(a, 1)) Then
            TrgWS.Cells(a, 1).Value = "Not Found"
        End If
    Next a
Next i
Application.DisplayAlerts = False
SrcWB.Close SaveChanges:=False
Application.DisplayAlerts = True
TrgWS.Activate
TrgWS.Range("A1").Select
Application.ScreenUpdating = True
MsgBox "Codes Updated!", vbInformation
End Sub

Open in new window

If I needed to change the source & Target column what am I changing
I'm asking for future

Thanx

Shums Faruk

Your comments are right, you understood correctly.

Have you tried VBA solution and worked as needed?

trevor1940

ASKER

Hi
I've tried it at home using test data not tried on actual data yet Hope to do that on Monday

trevor1940

ASKER

Hi
It turned out the default deliminator was a "|" not a coma
Typing the formula didn't reveal this
Opening your sample file Trevor1940_IndexMatch.xlsx instantly converted the coma's to pipes

Thanx for your help