Combine data from 2 sheets with different columns

[mulpuris]

Hi ,

I have to combine data from 2 sheets where I have 4 columns on Sheet1 and 4 columns on Sheet2. I would like to get the data from Sheet1(master) and Sheet2 onto a single sheet Sheet3 with all the records from Sheet1 along with only the matching data from Sheet2 based on Column "Code".The data in Sheet2 has multiple rows for same Code and I would like to have all these records with the same Code transposed horizontally into a single row.

I have provided the actual data in Sheet1 and Sheet2 along with the desired output data set in Sheet3.

Can any one help me on this Please.

Thanks in Advance

[nilem]

try it

Sub ertert()    'active sheet is Sheet3
Dim x, i&, s$, t, u

[a1].CurrentRegion.ClearContents
x = Sheets("Sheet1").Range("A1").CurrentRegion.Value

With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For i = 1 To UBound(x)
        s = Join(WorksheetFunction.Index(x, i, 0), "|")
        .Item(x(i, 1)) = s
    Next i

    x = Sheets("Sheet2").Range("A1").CurrentRegion.Value
    For i = 1 To UBound(x)
        If .Exists(x(i, 1)) Then
            s = x(i, 2) & "|" & x(i, 3) & "|" & x(i, 4)
            .Item(x(i, 1)) = .Item(x(i, 1)) & "|" & s
        End If
    Next i: i = 0

    For Each t In .items
        u = Split(t, "|"): i = i + 1
        Cells(i, 1).Resize(, UBound(u) + 1) = u
    Next t
End With
End Sub

[Mordred]

Please refer to your other thread located here.

Thread Closed

[NBVC]

mulpuris,

If you or a colleague has already asked this question here: http://www.excelforum.com/excel-prog...on-column.html, then please continue there. Do not duplicate posts and waste volunteers' time.