Get data from one sheet to another
 

I have 2 sheets in my file.

The first contains data from database1 and is made up of all cattle on a
farm at present and those which have moved off in the last 3 yeras.

The second contains data from database2 and is made up of cattle that
subsidies have been claimed on in the last 3 years.

Sheet1 may have thousands of records where Sheet2 will have a maximum of a
few hundred.

The only common field is the animals Eartag. What I want to do is get
information from 2 columns on Sheet2 and place it in Sheet1.

The locations of the columns are as follows:

I want column B on Sheet2 to go into column H on Sheet1 (Column H already
has data in it).

Then I would like column P on Sheet2 to go into column P on Sheet1(this
column is empty).

Any suggestions gratefully received.

Gareth

Get data from one sheet to another
 

Gareth,

You need VLOOKUP

Assuming Eartag is in column A on both sheets,

column B, sheet1
=VLOOKUP(A1,Sheet2!$A$1:$P$100,8,FALSE) - column H is 8

column P, sheet2
=VLOOKUP(A1,Sheet1!$A$1:$P$1,16,FALSE) - column P is 16

you will have to adjust the ranges and the Eartag column to suit your actual
columns, and then the offsets to match.

--

HTH

Bob Phillips

"Gareth" wrote in message
...
I have 2 sheets in my file.

The first contains data from database1 and is made up of all cattle on a
farm at present and those which have moved off in the last 3 yeras.

The second contains data from database2 and is made up of cattle that
subsidies have been claimed on in the last 3 years.

Sheet1 may have thousands of records where Sheet2 will have a maximum of a
few hundred.

The only common field is the animals Eartag. What I want to do is get
information from 2 columns on Sheet2 and place it in Sheet1.

The locations of the columns are as follows:

I want column B on Sheet2 to go into column H on Sheet1 (Column H already
has data in it).

Then I would like column P on Sheet2 to go into column P on Sheet1(this
column is empty).

Any suggestions gratefully received.

Gareth

Get data from one sheet to another
 

Thanks for this Bob, but I need to do it using VBA. I have tried but get
N/A in cells were the tag doesn't appear on Sheet2.

Can Iserror be used?

Grateful for any help.

Gareth


"Bob Phillips" wrote in message
...
Gareth,

You need VLOOKUP

Assuming Eartag is in column A on both sheets,

column B, sheet1
=VLOOKUP(A1,Sheet2!$A$1:$P$100,8,FALSE) - column H is 8

column P, sheet2
=VLOOKUP(A1,Sheet1!$A$1:$P$1,16,FALSE) - column P is 16

you will have to adjust the ranges and the Eartag column to suit your
actual
columns, and then the offsets to match.

--

HTH

Bob Phillips

"Gareth" wrote in message
...
I have 2 sheets in my file.

The first contains data from database1 and is made up of all cattle on a
farm at present and those which have moved off in the last 3 yeras.

The second contains data from database2 and is made up of cattle that
subsidies have been claimed on in the last 3 years.

Sheet1 may have thousands of records where Sheet2 will have a maximum of
a
few hundred.

The only common field is the animals Eartag. What I want to do is get
information from 2 columns on Sheet2 and place it in Sheet1.

The locations of the columns are as follows:

I want column B on Sheet2 to go into column H on Sheet1 (Column H
already
has data in it).

Then I would like column P on Sheet2 to go into column P on Sheet1(this
column is empty).

Any suggestions gratefully received.

Gareth

Get data from one sheet to another
 

Tom

Many thanks for this, with a little tweak or two it now works fine.

However I now have another problem, it is possible for the same eartag to
appear twice on Sheet2. I want the value in column P of both rows to appear
in column P on Sheet1.

For example, UK F2611 00231 may appear twice on Sheet2 with a 'C' and an 'L'
in column P, I would like 'CL' to be displayed in column P on Sheet1 for UK
F2611 00231.

Can Lookup look for the same value twice?

Gareth

This is the code as it is now:

Sub InsertBandCorL()
Application.ScreenUpdating = False
With Worksheets("Cattle Details")
Set rng = .Range(.Cells(2, 3), .Cells(Rows.Count, 3).End(xlUp))
End With
With Worksheets("Retention Periods")
Set rng1 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng
res = Empty
res = Application.VLookup(cell.Value, rng1.Resize(, 16), 2, 0)
If Not IsError(res) Then
cell.Offset(0, 5).Value = res
cell.Offset(0, 13).Value = Application.VLookup(cell.Value, rng1.Resize(,
16), 16, 0)
End If
Next
Application.ScreenUpdating = True
End Sub

"Tom Ogilvy" wrote in message
...
The worksheet formula wouldn't work in Column H if you already have data
there and want to concatenate to that data unless you want to use a dummy
column, then paste back over column H. This macro will append the data
from
column B to Column H. It assumes the eartag value is in column A on each
sheet and values start in row 2.

Sub Tester1()

With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
With Worksheets("Sheet2")
Set rng1 = .Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng
res = Empty
res = Application.VLookup(cell.Value, _
rng1.Resize(, 16), 2, 0)
If Not IsError(res) Then
cell.Offset(0, 7).Value = _
cell.Offset(0, 7).Value & " " & res
cell.Offset(0, 15).Value = _
Application.VLookup(cell.Value, _
rng1.Resize(, 16), 16, 0)
End If
Next
End Sub

Make a backup of your workbook before running the macro - test on the
backup
copy.

--
Regards,
Tom Ogilvy

Gareth wrote in message
...
I have 2 sheets in my file.

The first contains data from database1 and is made up of all cattle on a
farm at present and those which have moved off in the last 3 yeras.

The second contains data from database2 and is made up of cattle that
subsidies have been claimed on in the last 3 years.

Sheet1 may have thousands of records where Sheet2 will have a maximum of
a
few hundred.

The only common field is the animals Eartag. What I want to do is get
information from 2 columns on Sheet2 and place it in Sheet1.

The locations of the columns are as follows:

I want column B on Sheet2 to go into column H on Sheet1 (Column H
already
has data in it).

Then I would like column P on Sheet2 to go into column P on Sheet1(this
column is empty).

Any suggestions gratefully received.

Gareth

Get data from one sheet to another
 

If the eartag can only appear twice, then this clumsy modification will
work:

Sub Tester1()

With Worksheets("Sheet1")
Set rng = .Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
With Worksheets("Sheet2")
Set rng1 = .Range(.Cells(2, 1), _
.Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng
res = Empty
res = Application.VLookup(cell.Value, _
rng1.Resize(, 16), 2, 0)
If Not IsError(res) Then
cell.Offset(0, 7).Value = _
cell.Offset(0, 7).Value & " " & res
cell.Offset(0, 15).Value = _
Application.VLookup(cell.Value, _
rng1.Resize(, 16), 16, 0)
res1 = Application.Match(cell.Value, _
rng1, 0)
Set rng2 = rng1(res1 + 1)
Set rng2 = rng1.Parent.Range( _
rng2, rng1(rng1.Count))
res2 = Application.VLookup(cell.Value, _
rng2.Resize(, 16), 16, 0)
If Not IsError(res2) Then
cell.Offset(0, 15).Value = _
cell.Offset(0, 15).Value & res2
End If
End If
Next
End Sub

If it can appear more than twice, then I probably would use an entirely
different, more generalized approach.


Regards,
Tom Ogilvy



Gareth wrote in message
...
Tom

Many thanks for this, with a little tweak or two it now works fine.

However I now have another problem, it is possible for the same eartag to
appear twice on Sheet2. I want the value in column P of both rows to
appear
in column P on Sheet1.

For example, UK F2611 00231 may appear twice on Sheet2 with a 'C' and an
'L'
in column P, I would like 'CL' to be displayed in column P on Sheet1 for
UK
F2611 00231.

Can Lookup look for the same value twice?

Gareth

This is the code as it is now:

Sub InsertBandCorL()
Application.ScreenUpdating = False
With Worksheets("Cattle Details")
Set rng = .Range(.Cells(2, 3), .Cells(Rows.Count, 3).End(xlUp))
End With
With Worksheets("Retention Periods")
Set rng1 = .Range(.Cells(2, 1), .Cells(Rows.Count, 1).End(xlUp))
End With
For Each cell In rng
res = Empty
res = Application.VLookup(cell.Value, rng1.Resize(, 16), 2, 0)
If Not IsError(res) Then
cell.Offset(0, 5).Value = res
cell.Offset(0, 13).Value = Application.VLookup(cell.Value, rng1.Resize(,
16), 16, 0)
End If
Next
Application.ScreenUpdating = True
End Sub