UDF to count letters in a range

[Cynthia Moore]

I need to get a count of the number of occurrences of each of the letters (A-Z) in a range.

The input range contains names like this:

ALICE
FRED
SALLY
MARK
HENRY
JUDY

The output would be another range like this

A 3
B 0
C 1
D 2
E 3
… …
Z 0

I'd like to be able to call the UDF like this: =LetterTally(B2:B75,E2).

B2:B75 is the input range.
E2 is the upper left hand corner of the output range.

The names should all be upper case, but the tally should be case insensitive (A=a).

Can someone get me started? I am having trouble setting up the output array and using the letters to index into it?

I'm also having trouble with how to write the result to the sheet. If this is not possible, it's OK to write to a text file or even just display it on the screen.

Thanks

[pjwhitfield]

Unfortunately its late and my minds not working fully but this will calculate what you're after, trouble is the outputting isnt working so ive output to message box for test purposes:

Please Login or Register  to view this content.

[martindwilson]

a formula to do it
=SUMPRODUCT(--(MID($A$1:$A$100,COLUMN(1:255),1)=E1)) where e1:a26 = a through z

[Cynthia Moore]

Unfortunately its late and my minds not working fully but this will calculate what you're after, trouble is the outputting isn't working so I've output to message box for test purposes:

This does seem to work once I got all the variables properly declared. I run with Option Explicit.

[Cynthia Moore]

a formula to do it
=SUMPRODUCT(--(MID($A$1:$A$100,COLUMN(1:255),1)=E1)) where E1:E26 = a through z

Wow. That's amazing! (I had to fix the typo "e1:a26".)

What does the "COLUMN(1:255)" do?

Can this be modified to work on a 2-dimensional range?

Please Login or Register  to view this content.

I tried it, but got an error.

[martindwilson]

so do i! but row(1:255) in mid() gives
say mid(a1,row(1:255),1) each letter in turn essentially {mid(a1,1,1)&";"mid(a1,2,1)&";"mid(a1,3,1).....&";"mid(a1,255,1)}
but i think i made an error this is quicker
=SUMPRODUCT(--(MID($A$1:A20,COLUMN(A:IU),1)=E1))

[AlKey]

Here is another version to count letters

=SUMPRODUCT(LEN($B$2:$B$75)-LEN(SUBSTITUTE($B$2:$B$75,E2,"")))

[humdingaling]

using modified code from PJ
Don't know how to output range with UDF...let alone a different range

caters for spaces and is not case sensitive


highlight area you want done
modify output range in code to whatever you want

[jindon]

UDF

Use in cell like
=IFERROR(LetterTally($B$2:$B$7,ROW(A1),COLUMN(A1)),"")
Copy to the right adjacent cell then copy both cells down

Please Login or Register  to view this content.

[Cynthia Moore]

Thanks, everyone, for the helpful responses. I have something that works. Much appreciation.