how to count duplicate names excel

Question

I have a single column of data in Column A that looks like this:

Joe 
Joe 
Joe
John
John
Josh
Josh
Josh
Josh

Can someone please provide me with code that would sum the number of Joes, Johns, and Joshs and put the sum for each name in the adjacement column. Thank you in advance! Huge help.. I have 5000 rows of names

Answer 1

---

[Note]

The meaning of the question has been change. My answer refer to the original version of question.

---

You can use Dictionary class to get count of each name in a string. Please, see:

'needs reference to Sctipting Runtime dll
Sub DoSomething()
Dim s As String, result() As String
Dim i As Integer, counter As Integer
Dim dic As Dictionary, k As Variant

s = "Joe Joe Joe John John Josh Josh Josh Josh"

result = Split(s, " ")
Set dic = New Dictionary
With dic
    .CompareMode = BinaryCompare
    For i = LBound(result) To UBound(result)
        If Not .Exists(result(i)) Then
            .Add Key:=result(i), Item:=1
        Else
            k = dic(result(i))
            dic(result(i)) = k + 1
        End If
    Next
End With

For Each k In dic.Keys
    Debug.Print k, dic(k)
Next

Set dic = Nothing

End Sub

Result:

Joe            3 
John           2 
Josh           4 

[EDIT]

As to the changed question, you have to change only one loop. instead of:

For i = LBound(result) To UBound(result)
'
Next 

use:

'earlier (variable declaration section):
Dim wsh As Worksheet

'later:
Set wsh = Thisworkbook.Worksheets("SheetName")
i = 2
Do While wsh.Range("A" & i) <>""
    If Not .Exists(wsh.Range("A" & i)) Then
        .Add Key:=wsh.Range("A" & i), Item:=1
    Else
        k = dic(wsh.Range("A" & i))
        dic(wsh.Range("A" & i)) = k + 1
    End If
    i = i +1
Loop

Final note:

I'd suggest to move your focus on array formula, which enables you to make any calculation.

Steps to do (MS Excel 2010 and higher):

1) Copy column A into new Sheet

2) Remove duplicates (use Menu)

3) Select column B and insert the following formula:

=SUM(IF((Sheet1!$A$1:$A$1000=$A1), 1, 0))

4) Accept fomula by pressing CTRL + SHIFT + ENTER