I think if you run a macro like this:
Sub macro_1()
Dim count, pattern_array, no_patterns, i
no_patterns = Evaluate("=SUMPRODUCT((A2:A1000<>"""")/COUNTIF(A2:A1000,A2:A1000&""""))")
ReDim pattern_array(1 To no_patterns, 1 To 1)
For count = 2 To Range("A" & Rows.count).End(xlUp).Row
    If Range("A" & count - 1) <> Range("A" & count) Then i = i + 1
    pattern_array(i, 1) = pattern_array(i, 1) & "," & Range("B" & count)
Next count
Range("D2:D" & no_patterns + 1).Value = pattern_array
Range("E2:E" & no_patterns + 1).Formula = "=countif(D$2:D$" & no_patterns + 1 & ",D2)"
End Sub
on your example it will put the results you require in columns D and E.