I can just do it with VBA, maybe somebody else has a formula approach.
Click the button in the attached example.
This is the code used,
Sub Button1_Click()
Dim Rws As Long, Rng As Range, c As Range, x
Dim fws As Long, fng As Range, f As Range
Dim ws As Worksheet, sh As Worksheet
Dim s As String
Set ws = Sheets("Sheet1")
Set sh = Sheets("Sheet2")
With ws
Rws = .Cells(Rows.Count, "A").End(xlUp).Row
Set Rng = .Range(.Cells(1, 1), .Cells(Rws, 1))
End With
With sh
fws = .Cells(Rows.Count, "A").End(xlUp).Row
Set fng = .Range(.Cells(1, 1), .Cells(fws, 1))
End With
For Each c In Rng.Cells
s = ""
x = WorksheetFunction.CountIf(fng, c)
For Each f In fng.Cells
If c = f Then
y = IIf(x > 1, " ", "")
s = s + f.Offset(0, 1) & y
End If
Next f
c.Offset(0, 1) = s
Next c
End Sub
Bookmarks