Say I have a cell containing the following : [A, B, C, B, A]
. How would I be able to get the unique values along with their counts? ex. A = 2
, B = 2
, C = 1
0
votes
1 Answers
0
votes
Select the cell containing the data and run this short VBA macro:
Sub WhatsInIt()
Dim s As String, arr
Dim c As Collection, a
Dim i As Long, msg As String
Set c = New Collection
msg = ""
s = ActiveCell.Text
s = Mid(s, 2, Len(s) - 2)
arr = Split(s, ",")
On Error Resume Next
For Each a In arr
c.Add a, CStr(a)
Next a
On Error GoTo 0
For i = 1 To c.Count
msg = msg & vbCrLf & c.Item(i) & vbTab & aCount(c.Item(i), arr)
Next i
MsgBox msg
End Sub
Public Function aCount(st As String, ary As Variant) As Long
Dim ar
aCount = 0
For Each ar In ary
If ar = st Then aCount = aCount + 1
Next ar
End Function
EDIT#1:
This version puts the result in cells:
Sub WhatsInIt2()
Dim s As String, arr
Dim c As Collection, a
Dim i As Long, msg As String
Set c = New Collection
msg = ""
s = ActiveCell.Text
s = Mid(s, 2, Len(s) - 2)
arr = Split(s, ",")
On Error Resume Next
For Each a In arr
c.Add a, CStr(a)
Next a
On Error GoTo 0
With ActiveCell
For i = 1 To c.Count
.Offset(i - 1, 1).Value = c.Item(i)
.Offset(i - 1, 2).Value = aCount(c.Item(i), arr)
Next i
End With
End Sub
Public Function aCount(st As String, ary As Variant) As Long
Dim ar
aCount = 0
For Each ar In ary
If ar = st Then aCount = aCount + 1
Next ar
End Function
(the aCount() function is unchanged)