I am looking for a way to count individual letters and numbers in a worksheet
have 2 columns 1 containing names and 1 containing numbers and would like
individual counts (number of times text letters and numbers repeat )
numbers 0-9 and A-Z contained in the worksheet columns.

Can anyone help, I have tried and I am stuck......

Alicia

Try this:

A1:A10 = names
B1:B10 = numbers

Enter this formula in D1:

=CHAR(ROW(A65))

Enter this formula in E1:

=SUMPRODUCT(LEN(A$1:A$10)-LEN(SUBSTITUTE(UPPER(A$1:A$10),D1,"")))

Select both D1 and E1 and copy down to row 26

Enter this formula in G1:

=CHAR(ROW(A48))

Enter this formula in H1:

=SUMPRODUCT(LEN(B$1:B$10)-LEN(SUBSTITUTE(B$1:B$10,G1,"")))

Select both G1 and H1 and copy down to row 10

Biff

"Alicia" wrote in message
...
I am looking for a way to count individual letters and numbers in a
worksheet
have 2 columns 1 containing names and 1 containing numbers and would like
individual counts (number of times text letters and numbers repeat )
numbers 0-9 and A-Z contained in the worksheet columns.

Can anyone help, I have tried and I am stuck......

Alicia

Thank you, thank you, thank you !

Awesome, it worked, have been doing counts for quite a while and this will
certainly be a great help......Huge thank you !, will be a big time saver for
me.

I do have one more question, now that I have it set up on a worksheet,
formatted and prettied up with borders and such - how can I add column
headings without effecting the formulas ?

By the way, did I say thank you.... : )

Alicia

"T. Valko" wrote:

Try this:

A1:A10 = names
B1:B10 = numbers

Enter this formula in D1:

=CHAR(ROW(A65))

Enter this formula in E1:

=SUMPRODUCT(LEN(A$1:A$10)-LEN(SUBSTITUTE(UPPER(A$1:A$10),D1,"")))

Select both D1 and E1 and copy down to row 26

Enter this formula in G1:

=CHAR(ROW(A48))

Enter this formula in H1:

=SUMPRODUCT(LEN(B$1:B$10)-LEN(SUBSTITUTE(B$1:B$10,G1,"")))

Select both G1 and H1 and copy down to row 10

Biff

"Alicia" wrote in message
...
I am looking for a way to count individual letters and numbers in a
worksheet
have 2 columns 1 containing names and 1 containing numbers and would like
individual counts (number of times text letters and numbers repeat )
numbers 0-9 and A-Z contained in the worksheet columns.

Can anyone help, I have tried and I am stuck......

Alicia

Just move the formula ranges down 1 row.

For example, if the letter formulas are in the range D1:E26, select that
range of cells and "grab" the border of the range with your mouse then drag
it down 1 row so that the new range is D2:E27. Then you can put headers in
row 1.

Biff

"Alicia" wrote in message
...
Thank you, thank you, thank you !

Awesome, it worked, have been doing counts for quite a while and this will
certainly be a great help......Huge thank you !, will be a big time saver
for
me.

I do have one more question, now that I have it set up on a worksheet,
formatted and prettied up with borders and such - how can I add column
headings without effecting the formulas ?

By the way, did I say thank you.... : )

Alicia

"T. Valko" wrote:

Try this:

A1:A10 = names
B1:B10 = numbers

Enter this formula in D1:

=CHAR(ROW(A65))

Enter this formula in E1:

=SUMPRODUCT(LEN(A$1:A$10)-LEN(SUBSTITUTE(UPPER(A$1:A$10),D1,"")))

Select both D1 and E1 and copy down to row 26

Enter this formula in G1:

=CHAR(ROW(A48))

Enter this formula in H1:

=SUMPRODUCT(LEN(B$1:B$10)-LEN(SUBSTITUTE(B$1:B$10,G1,"")))

Select both G1 and H1 and copy down to row 10

Biff

"Alicia" wrote in message
...
I am looking for a way to count individual letters and numbers in a
worksheet
have 2 columns 1 containing names and 1 containing numbers and would
like
individual counts (number of times text letters and numbers repeat )
numbers 0-9 and A-Z contained in the worksheet columns.

Can anyone help, I have tried and I am stuck......

Alicia

Ok, got it its working fine, now it continues.....sorry

1) First question, how would I add a column for quantity per name and number
say A1 name and number would be 4 in the quantity and the next A2 would only
be 1 in the quantity didn't ask this initially and should have, obviously I
can sort by quantity but wanted to know if there was a way to have this
figured in since quantity amounts can vary quite a bit throughout my data ??

column A column B column C
name number quantity

SMITH 10 4
WILSON 25 1
ROBERTS 33 2


2) I understand the character reference now and have added counts for lower
case letters a,c,e & N with a tilde - char97, 99, 101 & 126 respectively. N
with the tilde is fine but it figures in the count for upper and lower case
letters a,c & e - have tried a couple of things but I am stuck....I thought
the upper and lower reference in the formula would take care of it but it
doesn't it gives the total count for whether its upper or lowercase alpha a,c
or e for both characters......


Hope you can help....

Alicia


"T. Valko" wrote:

Just move the formula ranges down 1 row.

For example, if the letter formulas are in the range D1:E26, select that
range of cells and "grab" the border of the range with your mouse then drag
it down 1 row so that the new range is D2:E27. Then you can put headers in
row 1.

Biff

"Alicia" wrote in message
...
Thank you, thank you, thank you !

Awesome, it worked, have been doing counts for quite a while and this will
certainly be a great help......Huge thank you !, will be a big time saver
for
me.

I do have one more question, now that I have it set up on a worksheet,
formatted and prettied up with borders and such - how can I add column
headings without effecting the formulas ?

By the way, did I say thank you.... : )

Alicia

"T. Valko" wrote:

Try this:

A1:A10 = names
B1:B10 = numbers

Enter this formula in D1:

=CHAR(ROW(A65))

Enter this formula in E1:

=SUMPRODUCT(LEN(A$1:A$10)-LEN(SUBSTITUTE(UPPER(A$1:A$10),D1,"")))

Select both D1 and E1 and copy down to row 26

Enter this formula in G1:

=CHAR(ROW(A48))

Enter this formula in H1:

=SUMPRODUCT(LEN(B$1:B$10)-LEN(SUBSTITUTE(B$1:B$10,G1,"")))

Select both G1 and H1 and copy down to row 10

Biff

"Alicia" wrote in message
...
I am looking for a way to count individual letters and numbers in a
worksheet
have 2 columns 1 containing names and 1 containing numbers and would
like
individual counts (number of times text letters and numbers repeat )
numbers 0-9 and A-Z contained in the worksheet columns.

Can anyone help, I have tried and I am stuck......

Alicia

Sorry, but I'm not following you on either question.

Biff

"Alicia" wrote in message
...
Ok, got it its working fine, now it continues.....sorry

1) First question, how would I add a column for quantity per name and
number
say A1 name and number would be 4 in the quantity and the next A2 would
only
be 1 in the quantity didn't ask this initially and should have, obviously
I
can sort by quantity but wanted to know if there was a way to have this
figured in since quantity amounts can vary quite a bit throughout my data
??

column A column B column C
name number quantity

SMITH 10 4
WILSON 25 1
ROBERTS 33 2


2) I understand the character reference now and have added counts for
lower
case letters a,c,e & N with a tilde - char97, 99, 101 & 126 respectively.
N
with the tilde is fine but it figures in the count for upper and lower
case
letters a,c & e - have tried a couple of things but I am stuck....I
thought
the upper and lower reference in the formula would take care of it but it
doesn't it gives the total count for whether its upper or lowercase alpha
a,c
or e for both characters......


Hope you can help....

Alicia


"T. Valko" wrote:

Just move the formula ranges down 1 row.

For example, if the letter formulas are in the range D1:E26, select that
range of cells and "grab" the border of the range with your mouse then
drag
it down 1 row so that the new range is D2:E27. Then you can put headers
in
row 1.

Biff

"Alicia" wrote in message
...
Thank you, thank you, thank you !

Awesome, it worked, have been doing counts for quite a while and this
will
certainly be a great help......Huge thank you !, will be a big time
saver
for
me.

I do have one more question, now that I have it set up on a worksheet,
formatted and prettied up with borders and such - how can I add column
headings without effecting the formulas ?

By the way, did I say thank you.... : )

Alicia

"T. Valko" wrote:

Try this:

A1:A10 = names
B1:B10 = numbers

Enter this formula in D1:

=CHAR(ROW(A65))

Enter this formula in E1:

=SUMPRODUCT(LEN(A$1:A$10)-LEN(SUBSTITUTE(UPPER(A$1:A$10),D1,"")))

Select both D1 and E1 and copy down to row 26

Enter this formula in G1:

=CHAR(ROW(A48))

Enter this formula in H1:

=SUMPRODUCT(LEN(B$1:B$10)-LEN(SUBSTITUTE(B$1:B$10,G1,"")))

Select both G1 and H1 and copy down to row 10

Biff

"Alicia" wrote in message
...
I am looking for a way to count individual letters and numbers in a
worksheet
have 2 columns 1 containing names and 1 containing numbers and would
like
individual counts (number of times text letters and numbers repeat )
numbers 0-9 and A-Z contained in the worksheet columns.

Can anyone help, I have tried and I am stuck......

Alicia

Can you deal with a macro?

Sub CountLetters()
''a count of each letter and number in used range outputted to a new sheet
Dim letCount(1 To 43) 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 "[0-9a-zA-Z]" Then
letCount(Asc(Mid(UCase(cell), ii, 1)) - 47) = _
letCount(Asc(Mid(UCase(cell), ii, 1)) - 47) + 1
End If
Next ii
Next cell
Set CopytoSheet = Worksheets.Add
CopytoSheet.Name = "ListLetters"
CopytoSheet.Activate
Range("B1").Resize(43, 1).Value = Application.Transpose(letCount)
With Range("A1").Resize(43, 1)
.Formula = "=char(row()+47)"
.Value = .Value
End With
Range("A11:A17").EntireRow.Delete
End Sub


Gord Dibben MS Excel MVP

On Wed, 14 Mar 2007 19:19:16 -0700, Alicia
wrote:

I am looking for a way to count individual letters and numbers in a worksheet
have 2 columns 1 containing names and 1 containing numbers and would like
individual counts (number of times text letters and numbers repeat )
numbers 0-9 and A-Z contained in the worksheet columns.

Can anyone help, I have tried and I am stuck......

Alicia

Hi Alicia

The COUNTIF function is the one you need.

B C D E

1 Tom
2 Dick =COUNTIF(C1:C5,"*o*")
3 Harry =COUNTIF(B1:B5,2)
2 George
5 Fred

The * in the first example is a wildcard thus counts cells with an o that
may have other characters on either side.

Hope this helps.