Sum duplicate names in a column VBA

[job432]

Hi everyone,

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

 

[FryGirl]

Why not just create a pivot table?

 

[job432]

Thank you for the quick response @FryGirl. I am not familiar with pivot tables. Would you mind instructing me, please?

 

[Rick Rothstein]

If you still want to use a macro, consider this one (it assumes you have nothing in Columns B or C)...

Sub CountNamesInColumnA()
  Dim LastRowA As Long, LastRowB As Long
  Application.ScreenUpdating = False
  Columns("B:C").Clear
  Range("A1", Cells(Rows.Count, "A").End(xlUp)).Copy Range("B1")
  Columns("B").RemoveDuplicates 1, xlNo
  LastRowA = Cells(Rows.Count, "A").End(xlUp).Row
  LastRowB = Cells(Rows.Count, "B").End(xlUp).Row
  With Range("C1:C" & LastRowB)
    .Formula = "=COUNTIF(A$1:A$" & LastRowA & ",B1)"
    .Value = .Value
  End With
  Application.ScreenUpdating = True
End Sub

 

[FryGirl]

Here is a tutorial on pivot tables

 

[job432]

You are the man! Thank you, Rick. Much appreciated

 

[job432]

Thank you as well, Gail! I am learning about pivot tables now

 

[Rick Rothstein]

You are the man! Thank you, Rick.

Well, of course I am "the man"... the only other responder was Gail (FryGirl) who, from her name, is surely female.

Oh, and of course, you are welcome.