Count occurance of names and list them with that number of times

[L. Howard]

LUp is a list of names. Unique, do dupes.
NmeRng is a list of names with many dupes of what is in the LUp list.

I want to count the number of times a name in LUp occurs in the list NmeRng and post it and the number of times it occurs in column M.

Like:
M. Mouse 8
D. Duck 11
Tom Saywer 4
etc.

This code attempt lists the name multiple times as M. Mouse 1, M. Mouse 2 and so on followed by the other names in same format.

Want one name and the number of times it occurs in NmeRng list.

Option Explicit
Sub CountNameList()

Dim i As Long
Dim LUp As Range
Dim NmeRng As Range
Dim b As Range
Dim c As Range

Set LUp = Range("C1:C" & Range("C" & Rows.count).End(xlUp).Row)
Set NmeRng = Range("A1:A" & Range("A" & Rows.count).End(xlUp).Row)
For Each c In LUp
  For Each b In NmeRng
    If b.Value = c Then
      i = i + 1
     End If
     Range("M2000").End(xlUp).Offset(1, 0) = b & " " & i
  Next
Next
End Sub

Thanks.

Howard

 

[JoeMo]

Try this:

Option Explicit
Sub CountNameList()

Dim i As Long
Dim LUp As Range
Dim NmeRng As Range
Dim c As Range
Dim Ct As Long

Set LUp = Range("C1:C" & Range("C" & Rows.Count).End(xlUp).Row)
Set NmeRng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
For Each c In LUp
  Ct = WorksheetFunction.CountIf(NmeRng, c.Value)
     Range("M2000").End(xlUp).Offset(1, 0) = c.Value & " " & Ct
    Ct = 0
Next c
End Sub

 

[L. Howard]

Thanks, JoeMo.

Spot on, I was trying to make more complicated than need be.


Appreciate it.

Howard

 

[Peter_SSs]

For a non-looping approach you could also consider this

Sub CountNameList_v2()
  Dim LUp As Range
  Dim NmeRng As Range
  
  Const f As String = "=^&"" ""&COUNTIF(#,^)"
  
  Set LUp = Range("C1", Range("C" & Rows.Count).End(xlUp))
  Set NmeRng = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
  With Range("M2000").End(xlUp).Offset(1).Resize(LUp.Rows.Count)
    .Formula = Replace(Replace(f, "^", LUp.Cells(1).Address(0, 0), 1, -1, 1), "#", NmeRng.Address)
    .Value = .Value
  End With
End Sub

Also, IF ..
- You had a heading in column A, and
- You don't need the column C list for any other purpose

.. then you could consider creating a Pivot Table directly from column A that could give you both the unique list and the count of the names.

 

[L. Howard]

Thanks Peter.

This forum is a vitural gold mine if info.

I presume the non-loop solution would be much faster if the search list is immense?

Have to admit I break out in hive when it comes to Pivot Tables. A problem I should get over. I sure do see them mentioned in a ton of solutions offered.

Thanks again to JoeMo and you, Peter.

Regards,
Howard

 

[Peter_SSs]

Thanks Peter.

This forum is a vitural gold mine if info.

I presume the non-loop solution would be much faster if the search list is immense?

Have to admit I break out in hive when it comes to Pivot Tables. A problem I should get over. I sure do see them mentioned in a ton of solutions offered.

Thanks again to JoeMo and you, Peter.

Regards,
Howard

Depends a bit on what you mean by "immense".
This should be faster, though it isn't as intuitive to follow.

Sub CountNameList_v3()
  Dim Data, Results
  Dim i As Long, j As Long, rws As Long
  Dim s As String
  
  Data = Range("A1", Range("A" & Rows.Count).End(xlUp))
  rws = UBound(Data, 1)
  ReDim Results(1 To rws, 1 To 1)
  With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To rws
      s = Data(i, 1)
      If .Exists(s) Then
        .Item(s) = .Item(s) + 1
      Else
        .Add s, 1
        j = j + 1
        Results(j, 1) = s
      End If
    Next i
    For i = 1 To j
      Results(i, 1) = Results(i, 1) & " " & .Item(Results(i, 1))
    Next i
  End With
  Range("M2000").End(xlUp).Offset(1).Resize(j).Value = Results
End Sub

 

[L. Howard]

Hi Peter,

I ran JoeMo and your V2 code on an 18,000+ list of the 'multiple names/dupes' using three names in the C column list and both worked in a half blink of time.

I'll archive the V3 code with the others, but if it runs any faster it will be done before it starts.<g>

Reards,
Howard

 

[Peter_SSs]

.. if it runs any faster it will be done before it starts.

I like that, but it turns out my claim was false and my testing showed my v3 code considerably slower than the other two which were very similar in run-times - as you noted with your technical measurement "both worked in a half blink of time."