vba - how to count duplicate names excel -

i have single column of data in column looks this:

joe  joe  joe john john josh josh josh josh 

can please provide me code sum number of joes, johns, , joshs , put sum each name in adjacement column. thank in advance! huge help.. have 5000 rows of names

---

[note]

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

---

you can use dictionary class count of each name in string. please, see:

'needs reference sctipting runtime dll sub dosomething() dim s string, result() string dim integer, counter integer dim dic dictionary, k variant  s = "joe joe joe john john josh josh josh josh"  result = split(s, " ") set dic = new dictionary dic     .comparemode = binarycompare     = lbound(result) ubound(result)         if not .exists(result(i))             .add key:=result(i), item:=1         else             k = dic(result(i))             dic(result(i)) = k + 1         end if     next end  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 changed question, have change 1 loop. instead of:

for = lbound(result) ubound(result) ' next  

use:

'earlier (variable declaration section): dim wsh worksheet  'later: set wsh = thisworkbook.worksheets("sheetname") = 2 while wsh.range("a" & i) <>""     if not .exists(wsh.range("a" & i))         .add key:=wsh.range("a" & i), item:=1     else         k = dic(wsh.range("a" & i))         dic(wsh.range("a" & i)) = k + 1     end if     = +1 loop 

final note:

i'd suggest move focus on array formula, enables make calculation.

steps (ms excel 2010 , higher):

1) copy column a new sheet

2) remove duplicates (use menu)

3) select column b , insert following formula:

=sum(if((sheet1!$a$1:$a$1000=$a1), 1, 0))

4) accept fomula pressing ctrl + shift + enter