Excel - Combining info from 2 sheets

December 2016


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.


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
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
 End With 'sheet1
    Application.CutCopyMode = False
End Sub


Thanks to venkat1926 for this tip on the forum.

Related :

This document entitled « Excel - Combining info from 2 sheets » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.