Excel - Combining info from 2 sheets

January 2017


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.


Published by aakai1056. Latest update on February 19, 2010 at 02:23 AM by aakai1056.
This document, titled "Excel - Combining info from 2 sheets," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).