Count the number of times a text pattern is used

Yes, I get your example to work, but when I amended the code for extra lines, I get error 400. I have attached the finished spreadsheet.

thanks
David

Sorry, I just noticed in your main examples the patterns do not appear consecutively as they did in their example. You also need to sort the results before going through and deleting duplicates:

Sub macro_1()
Dim count, pattern_array, no_patterns, i
no_patterns = Round(Evaluate("=SUMPRODUCT((A2:A13000<>"""")/COUNTIF(A2:A13000,A2:A13000&""""))"), 0)
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
With Range("E2:E" & no_patterns + 1)
    .Formula = "=countif(D$2:D$" & no_patterns + 1 & ",D2)"
    .Value = .Value
End With
Range("D2:E" & no_patterns + 1).Sort key1:=Range("D2")
For count = Range("D" & Rows.count).End(xlUp).Row To 2 Step -1
  If Range("D" & count) = Range("D" & count - 1) Then
    Range("D" & count & ":E" & count).Delete xlUp
  Else
    Range("D" & count) = Right(Range("D" & count), Len(Range("D" & count)) - 1)
  End If
Next
End Sub