Get data from one sheet to another

  • Thread starter Thread starter Gareth
  • Start date Start date
G

Gareth

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
 
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.
 
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
 
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
 
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
 
Back
Top