combining excel worksheets with common columns

[Guest]

I am using Excel 2003have 2 worksheets, one with a full list of job data (one
record per row) and the other with similar data but only a sub-set of the
first, where one column is common to both worksheets. I want to copy the
columns from the second worksheet onto the first one but only when the common
data matches (in this case a unique number).
Example:
Sheet1 headings: job_no, name, date, address
Sheet2 headings: job_no, status
Result needed: job_no, name, date, address, status

 

[Tom Ogilvy]

use the vlookup worksheet function in Sheet1.

See Excel help for details.

in E2 of sheet1:
=if(iserror(match(A2,Sheet2!A:A,0)),"",vlookup(A2,Sheet2!A:B,2,0))

drag fill down column E.

 

[Guest]

Thanks Tom
I probably should have been more detailed about my query. I have already
used the vlookup function (although thanks for the bit about getting rid of
the #N?A errors) an whilst it does work, excel runs it very slowly.
My main difficulty is that my two worksheets are the result of data
extraction which changes daily, increasing the data on each worksheet. I need
to automate the process of combining the two and have written a macro which
sort of does it but sometimes falls over because of the numbers of rows.
I have looked at the excel functions and "consolidate" may work but I don't
know how this operates in a macro.

 

[Tom Ogilvy]

So use Vlookup in your macro.

 

[Tom Ogilvy]

Sub AddData()
Dim rng1 as Range, rng2 as Range
Dim cell1 as Range, cell2 as Range
Dim res as Variant
with worksheets("Sheet1")
set rng1 = .Range(.Cells(2,1),.Cells(rows.count,1),End(xlup))
End With
With Worksheets("Sheet2")
set rng2 = .Range(.Cells(2,1),.Cells(rows.count,1).End(xlup))
End with
for each cell1 in rng1
res = Application.Match(cell1,rng2,0)
if not iserror(res) then
set cell2 = rng2(res).offset(0,1)
cell1.offset(0,4).Value = cell2.Value
end if
Next
End sub

 

[Guest]

Thanks Tom
You have a much better understanding of macros than I do. Can you recommend
any good books on the subject?
Regards
Robert

 

[Tom Ogilvy]

http://www.j-walk.com/ss/excel

look on the left for a link to his books. (John Walkenbach).

His power programming series is very good.

Rob Bovey/Stephen Bullen/John Green's Excel 2002 VBA Programmer's Reference
http://www.oaltd.co.uk/ExcelProgRef/Default.htm