Counting Instances

[saman110 via OfficeKB.com]

I have a column with many rows that may have blank cells in between them. how
can I get the instance or count of them?

For example in A column I have;

Q
S
Q
Q
H
H
C

Results should be;

3 Q
1 S
2 H
1 C

 

[Gord Dibben]

Since you're in the Programming group, here's a Tom Ogilvy macro.

Sub CountLetters()
''a count of each letter in a range outputted to a new sheet
Dim letCount(1 To 26) As Long
Dim wkSht As Worksheet
Dim ii As Long
Dim cell As Range
Dim WrkRng As Range
For Each wkSht In Worksheets
With wkSht
If .Name = "ListLetters" Then
Application.DisplayAlerts = False
Sheets("ListLetters").Delete
End If
End With
Next
Application.DisplayAlerts = True
Set WrkRng = ActiveSheet.UsedRange

For Each cell In WrkRng
For ii = 1 To Len(cell)
If Mid(UCase(cell), ii, 1) Like "[A-Z]" Then
letCount(Asc(Mid(UCase(cell), ii, 1)) - 64) = _
letCount(Asc(Mid(UCase(cell), ii, 1)) - 64) + 1
End If
Next ii
Next cell
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "ListLetters"
CopytoSheet.Activate
Range("B1").Resize(26, 1).Value = Application.Transpose(letCount)
With Range("A1").Resize(26, 1)
.Formula = "=char(row()+64)"
.Value = .Value
End With

End Sub


Gord Dibben MS Excel MVP

 

[Conan Kelly]

saman110,

Use a pivot table. Add the column to both the row area and the data area of
the pivot table. If the data area doesn't automatically select the count
function, go in and change it manully.

Look up using pivot tables in XL's Help.

HTH,

Conan

 

[saman110 via OfficeKB.com]

My column does not always have Alphabet. It may contain words.