transfering data from one sheet to another(merging)

  • Thread starter Thread starter adam
  • Start date Start date
A

adam

hi,
On sheet 1, the first column has the customer id# and other columns have
other details of that costumer(like: email, fax...)
On sheet 2 , the first column has the customer id#(doesn't have all the ID#
that sheet 1 has) and other columns have other information.

what can I do so it will look at each customer id# on sheet 2 look for for a
match on sheet 1 and add those additional columns from sheet 2 in that same
row on sheet1.
Please provide me the formula and explanation of the formula.
im unexperienced with this and need to do this on a very large sheet with
lots of data.
thank you.

example:
Sheet1:
CusID Phone Fax
1 XXX XXX
2 XXX XXX
3 XXX XXX
4 XXX XXX

Sheet 2
CusID Adress zip
2 XXX XXX
4 XXX XXX

MY result will be:
CusID Phone Fax Adress Zip
1 XXX XXX
2 XXX XXX XXX XXX
3 XXX XXX
4 XXX XXX XXX XXX

Thank you
 
Adam
What you want cannot be done with a formula. You need to use VBA
(programming).
The following macro will do what you want. I assumed your sheets are named
"One" and "Two". I also assumed your data started in row 2. Change these
as needed to work with what you have. HTH Otto
Sub CopyDups()
Dim rColAOne As Range
Dim rColATwo As Range
Dim RngToCopy As Range
Dim i As Range
Dim FoundRow As Long
Application.ScreenUpdating = False
Sheets("One").Select
Set rColAOne = Range("A2", Range("A" & Rows.Count).End(xlUp))
With Sheets("Two")
Set rColATwo = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
End With
For Each i In rColATwo
If Not rColAOne.Find(What:=i.Value, LookAt:=xlWhole) Is Nothing
Then
FoundRow = rColAOne.Find(What:=i.Value,
LookAt:=xlWhole).Row
With Sheets("Two")
Set RngToCopy = .Range(i.Offset(, 1), .Cells(i.Row,
Columns.Count).End(xlToLeft))
RngToCopy.Copy Cells(FoundRow,
Columns.Count).End(xlToLeft).Offset(, 1)
End With
End If
Next i
Application.ScreenUpdating = True
End Sub
 
Back
Top