Combine results when match is found

[DHayes]

Good afternoon,
I have been hunting for a solution but to no avail. I need to to match column A from sheet 1 to sheet 2. I need all results it finds in column B from sheet 1 and combine it into column B in sheet 2.
Sheet 1
[TABLE="class: grid, width: 443"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Company[/TD]
[TD]Qualification[/TD]
[TD]Number of Learners[/TD]
[/TR]
[TR]
[TD]45th Engen Convenience Centre[/TD]
[TD]Learnership[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]45th Engen Convenience Centre[/TD]
[TD]Skills Programmes[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]5 Ways Spar[/TD]
[TD]Learnership[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A AND D SPITZ PTY LTD[/TD]
[TD]Learnership[/TD]
[TD="align: right"]22[/TD]
[/TR]
[TR]
[TD]A AND D SPITZ PTY LTD[/TD]
[TD]Skills Programmes[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]

Desired results in sheet 2
Sheet 2
[TABLE="class: grid, width: 443"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]45th Engen Convenience Centre[/TD]
[TD]Learnership;Skills Programmes[/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD]5 Ways Spar[/TD]
[TD]Learnership[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]A AND D SPITZ PTY LTD[/TD]
[TD]Learnership;Skills Programmes[/TD]
[TD="align: right"]27[/TD]
[/TR]
</tbody>[/TABLE]

Any assistance will be appreciated.

 

[Aladin Akyurek]

In B2 of Sheet2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN(";",TRUE,IF(FREQUENCY(IF(1-(Sheet1!$B$2:$B$6=""),IF(Sheet1!$A$2:$A$6=$A2,MATCH(Sheet1!$B$2:$B$6,Sheet1!$B$2:$B$6,1))),ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),Sheet1!$B$2:$B$6,""))

In C2 of Sheet2 just enter and copy down:

=SUMIFS(Sheet1!$C$2:$C$6,Sheet1!$A$2:$A$6,$A2)

 

[Scott T]

See if this macro does what you want. This assumes Sheet 2 has headers in row 1

Sub combine()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lr1 As Long
Dim lr2 As Long
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
lr1 = ws1.Cells(Rows.Count, 2).End(xlUp).Row
lr2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row
ws2.Range("B1:B" & lr2).ClearContents

For x = 2 To lr2
    For y = 2 To lr1
        If ws1.Cells(y, 1) = ws2.Cells(x, 1) Then
            ws2.Cells(x, 2) = ws2.Cells(x, 2) & "; " & ws1.Cells(y, 2)
            If Left(ws2.Cells(x, 2), 1) = ";" Then ws2.Cells(x, 2) = Trim(Right(ws2.Cells(x, 2), Len(ws2.Cells(x, 2)) - 1))
                       
        End If
    Next y
Next x

End Sub

 

[CodeNinja]

Unfortunately, if you don't have excel 2016, you cannot use textjoin function... Therefore you have to use vlookupConcatinate. Oh wait... that doesn't exist either... so here it is...

Create a module and insert the following code:

Function vlookupConcatinate(lookupValue As String, tableArray As Range, columnIndex As Integer) As String
Dim rng As Range
For Each rng In tableArray.Columns(1).Cells
    If rng = lookupValue Then
        vlookupConcatinate = vlookupConcatinate & rng.Offset(0, columnIndex - 1) & "; "
    End If
Next rng
vlookupConcatinate = Mid(vlookupConcatinate, 1, Len(vlookupConcatinate) - 2)

End Function

Then use vlookupConcatinate the same way you would use vlookup to get your results:
In B2 of sheet 2: =vlookupConcatinate(A2,Sheet1!A1:C6,2) and copy down...

Good luck,

CN.

 

[DHayes]

Thank you Aladin, unfortunately I am using excel 2010.
Thank you Scot works perfectly.
Thank you CodeNinja it works perfectly.