Excel - Combining info from 2 sheets
Issue
How can I match and combine records across two excel sheets. I have two excel sheets containing record information, about 6 columns for each record. I would like to be able to match records by part # and combine the columns for each record from each sheet. So each new record will include all available columns from both sheets for each specific record (each record is a row). Part # in both sheets is column A, while the rest of the columns have different information. The formula needs to check for each part# in column A sheet 1 and find the same part # in column A in sheet 2 and then combine the information. There are 500 records so it will need to be able to do this automatically for all records.
The combined records could be displayed in a new sheet # 3 of combined within sheet 1 or 2.
If possible please also explain how to implement the formula since I am not excel savvy.
Solution
I assumed that there are no duplicates in column A of either sheet 1 or sheet2
the heading rows are in row1 in both the sheets.
Park your original file safely somewhere so that it can be retrieved
if there is a problem
Try this macro and see sheet 3 and confirm whether this is what you want:
Sub test()
Dim c As Range, cfind As Range, x, dest As Range, cfind1 As Range
On Error Resume Next
Worksheets("sheet3").Cells.Clear
With Worksheets("sheet1")
.UsedRange.Copy Worksheets("sheet3").Range("a1")
For Each c In Range(.Range("a2"), .Range("a2").End(xlDown))
x = c.Value
With Worksheets("sheet2")
Set cfind = .Cells.Find(what:=x, lookat:=xlWhole)
If cfind Is Nothing Then GoTo line1
.Range(cfind.Offset(0, 1), cfind.End(xlToRight)).Copy
With Worksheets("sheet3")
Set cfind1 = .Cells.Find(what:=x, lookat:=xlWhole)
If cfind1 Is Nothing Then GoTo line1
cfind1.End(xlToRight).Offset(0, 1).PasteSpecial
End With 'sheet3
End With 'sheet2
line1:
Next
End With 'sheet1
Application.CutCopyMode = False
End Sub
Note
Thanks to
venkat1926 for this tip on the forum.
See also
Knowledge communities.
