combining excel worksheets with common columns
 

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

combining excel worksheets with common columns
 

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.

--
Regards,
Tom Ogilvy

"robertlewis" wrote in message
...
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

combining excel worksheets with common columns
 

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" wrote:

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.

--
Regards,
Tom Ogilvy

"robertlewis" wrote in message
...
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

combining excel worksheets with common columns
 

So use Vlookup in your macro.

--
Regards,
Tom Ogilvy


"robertlewis" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"robertlewis" wrote in message
...
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

combining excel worksheets with common columns
 

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

--
Regards,
Tom Ogilvy



"robertlewis" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"robertlewis" wrote in message
...
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

combining excel worksheets with common columns
 

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" wrote:

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

--
Regards,
Tom Ogilvy



"robertlewis" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"robertlewis" wrote in message
...
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

combining excel worksheets with common columns
 

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
--
regards,
Tom Ogilvy


"robertlewis" wrote in message
...
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" wrote:

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

--
Regards,
Tom Ogilvy



"robertlewis" wrote in message
...
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" wrote:

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.

--
Regards,
Tom Ogilvy

"robertlewis" wrote in message
...
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