Excel "Join" Function

Question

I need to combine rows from two Excel worksheets into one long row, based on an ID column in each of the worksheets. Effectively I want to perform the equivilent of a SQL JOIN on two worksheets to produce a combined worksheet.

I can get a Range object for a row in the first worksheet, and then use the ID column to get a Range object for the corresponding row in the second worksheet. Now I need to combine them.

I am aware of Merge and Union VBA methods, however I don't think they do what I need.

How do I combine these two range objects?

eg: worksheet 1 row: a, b, c worksheet 2 row: d, e, f

combined row: a, b, c, d, e, f

Answer 1

Can you put a formula in column d of worksheet 1 to do a vlookup on worksheet 2 to find the matching ID and return the value. Then repeat this for the other columns e and f?

Answer 2

IF your 2 worksheets are in the same closed workbook, I think you could eventually use an SQL query against them using adodb.

dim cnn As ADODB.Connection
dim rst As ADODB.Recordset
strProv = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
                    SourceFile & ";Extended Properties=Excel 8.0;"
cnn.Open strProv
Set rst = New ADODB.Recordset
strSql = "select * from [Sheet1$]"
rst.Open strSql, cnn
rngDestRange.CopyFromRecordset rst

I have been using that technique, but for one table at a time. So I am not 100% sure the join is supported, but you could give it a try.

Answer 3

An alternative to Robin Day's solution is to use Match() to get the row position and then Index() to retrieve the data. This should be faster if you have lots of data. This is assuming a 1:1 relationship. The functions are of the form:

=MATCH(RowID, OtherTable, 0)

=INDEX(OtherTable, MATCH(), ColumnPosition)

Answer 4

Just look at the VLOOKUP function, with FALSE as its last argument. It does exactly what you want.