How can I count the number of times a phrase is repeated in a row?

[gohotels]

Hello, does anybody know how I can count the number of times each unique phrase in row "A" is repeated?

For example if my data set was

Blue
Green
Black
Green
Red
Red
Red
Red Hat

how can I get excel to count the number of times and return data like

Blue 1
Green 2
Black 1
Green 2
Red 3
Red 3
Red 3
Red Hat 1

Any help would be appreciated!

 

[jeffreybrown]

Try

B1 =COUNTIF($A$1:$A$8,A1)

 

[jolivanes]

Try jindon's code on a copy of your workbook

Sub test()
Dim e
With CreateObject("Scripting.Dictionary")
    For Each e In Sheets(1).Range("a2").CurrentRegion.Value
        If Not IsEmpty(e) Then .Item(e) = .Item(e) + 1
    Next
    For Each e In .keys
        If .Item(e) = 1 Then .Remove e
    Next
    Sheets(2).Cells(1).Resize(.Count, 2).Value = _
    Application.Transpose(Array(.keys, .items))
    Sheets(2).Cells(1).CurrentRegion.Columns(2) _
    .NumberFormat = "0"" Times"""
End With
End Sub

 

[scorpio_libra]

Use Pivot Table, fast and efficient