Possible Vlookup, or Index/Match or...

[Glaswegian]

Hi all

I'm pretty hopeless at formulae so would appreciate some help.

I have 2 workbooks, one contains around 250 rows of data that will not change. The other contains around 120,000 rows and is spread over 2 sheets - it is a data dump from one of our systems.

I need to search for data in the large workbook and copy one corresponding value, for each set of data matched, to the small workbook. The only way I can uniquely identify each record is by a combination of 2 cells - for example, in the small book

Col A B
6030 12345678
6451 98765432

Column B is an account number - but there will be duplicates in the large workbook. However, there will not be duplicates if col A & B are combined or used together.

If I can find some way to match a record in the small book to one in the large book, then I'd need to copy the value of col J in the large book to the corresponding matching entry in col S of the small book.

Any help gratefully received.

Thanks.

[mdmackillop]

[VBA]
Sub Test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim FirstAddress As String, c As Range
Set ws1 = Workbooks("Book1.xls").Sheets(1)
Set ws2 = Workbooks("Book2.xls").Sheets(1)
For Each cel In ws1.Columns(1).SpecialCells(xlCellTypeConstants)
FirstAddress = ""
Set c = ws2.Columns(1).Find(cel, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
If c.Offset(, 1) = cel.Offset(, 1) Then
cel.Offset(, 18) = c.Offset(, 9)
Exit Do
End If
Set c = ws2.Columns(1).FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
Next
End Sub
[/VBA]

[Glaswegian]

Hi Malcolm

Many thanks - I shall have a play with this.