Reputation: 9549
I have a list of Id's in one column. sometimes the cell has a single id, sometimes a cell has multiple id's:
[ "52b46bfc763f4ad9198b45ab", "533c0cba763f4a505e8b46db" ] - multiple id's
52c2ff91763f4a572d8b45e6 - single id
I'm using COUNTIF
to count all the instances of a given id. if there were only a single id in each cell, it would work. But, when there are more than one id in a cell it fails.
How do I count the number of times a given instance shows up in the column? Happy to use excel functions or VBA.
thanks
Upvotes: 0
Views: 94
Reputation: 12497
I like Gary's Student
approach for ease.
For reference here is a VBA solution using a user-defined function. Example:
A B
1 52c2ff91763f4a572d8b45e6 `=COUNTIDS(A1)` = 4
2 52c2ff91763f4a572d8b45e6, 52c2ff91763f4a572d8b45e6
3 52c2ff91763f4a572d8b45e6
4 52b46bfc763f4ad9198b45ab, 533c0cba763f4a505e8b46db
This code inside a VBA module:
Function CountIDs(id As Range)
Dim ids As Range, cl As Range, cnt As Long, arr As Variant, i As Integer
Set ids = Range("A1:A" & Range("A1").End(xlDown).Row) //amend to suit your set-up
cnt = 0
For Each cl In ids
If InStr(1, cl, ",", vbTextCompare) Then
arr = Split(cl, ",")
For i = 0 To UBound(arr)
If VBA.Trim(arr(i)) = id Then
cnt = cnt + 1
End If
Next i
Else
If cl = id Then
cnt = cnt + 1
End If
End If
Next
CountIDs = cnt
End Function
Upvotes: 1
Reputation: 96781
use wildcards:
=COUNTIF(A:A,"*52c2ff91763f4a572d8b45e6*")
for example:
Upvotes: 4