|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| |
i think you mis understood, i need to know the number of first names in my
data, that begin with each particuliar letter of the alphabet, "A" through "z"
the number of occurances of each letter in the alphabet? A-Z
ex.
a 10
b 9
c 3
d 29
"Dave Peterson" wrote:
> =countif(a1:b99,"a*")
>
>
>
> jonathan wrote:
> >
> > I have a nuber of cells that contain names, I have to count the names that
> > begin with with letter, a,through z, How do I do that?
> >
> > "jay" wrote:
> >
> > > For example: I have a number of cells that contains names, and I have to
> > > count the name that begins with letter "A", what do I do?
> > >
> > > many thanks
>
> --
>
> Dave Peterson
> |
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
 Reply |
| |
 |
|
 Top |
|
|
|
|
|
|
|
|
|
| |
I think this is a John Walkenbach construct but not positive.
I have altered it return just the first letter of a string.
Sub Count_First_Letters()
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 1
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
On Tue, 14 Oct 2008 20:26:00 -0700, jonathan
<jonathan@discussions.microsoft.com> wrote:
>i think you mis understood, i need to know the number of first names in my
>data, that begin with each particuliar letter of the alphabet, "A" through "z"
>
>the number of occurances of each letter in the alphabet? A-Z
>
>ex.
>
>a 10
>b 9
>c 3
>d 29
>
>
>"Dave Peterson" wrote:
>
>> =countif(a1:b99,"a*")
>>
>>
>>
>> jonathan wrote:
>> >
>> > I have a nuber of cells that contain names, I have to count the names that
>> > begin with with letter, a,through z, How do I do that?
>> >
>> > "jay" wrote:
>> >
>> > > For example: I have a number of cells that contains names, and I have to
>> > > count the name that begins with letter "A", what do I do?
>> > >
>> > > many thanks
>>
>> --
>>
>> Dave Peterson
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
|
|
|
|
| |
Hi Jonathan
Just modify Dave's formula.
Type a, b , c etc down column C, then use
=COUNTIF(A:A,C1&"*")
Copy down through cells C2:C26
--
Regards
Roger Govier
"jonathan" <jonathan@discussions.microsoft.com> wrote in message
news:EEAB652A-B232-4225-8E8A-3A0572ABDD0E@microsoft.com...
> i think you mis understood, i need to know the number of first names in my
> data, that begin with each particuliar letter of the alphabet, "A" through
> "z"
>
> the number of occurances of each letter in the alphabet? A-Z
>
> ex.
>
> a 10
> b 9
> c 3
> d 29
>
>
> "Dave Peterson" wrote:
>
>> =countif(a1:b99,"a*")
>>
>>
>>
>> jonathan wrote:
>> >
>> > I have a nuber of cells that contain names, I have to count the names
>> > that
>> > begin with with letter, a,through z, How do I do that?
>> >
>> > "jay" wrote:
>> >
>> > > For example: I have a number of cells that contains names, and I have
>> > > to
>> > > count the name that begins with letter "A", what do I do?
>> > >
>> > > many thanks
>>
>> --
>>
>> Dave Peterson
>>
|
| |
|
| |
Was this post helpful to you? |
|
|
|
|
|
|
|
Reply |
| |
|
|
Top |
|
|
|
|
|
Question
Answer
Notify me of replies