Assign a letter based on the total count

[Dragonphantom]

Problem:
I have a column of names ending with a total count of entries that can range, let's say 10-75, and I would like to assign a 5 letters to a new column evenly and preferably in order top down.
So what I have:

	A	B
1	Matt
2	Mark
3	Chris
4	Nate
5	Jake
6	Bill
7	Hank
8	Will
9	Jack
10	Jose
100	10

What I want:

	A	B
1	Matt	S
2	Mark	S
3	Chris	H
4	Nate	H
5	Jake	A
6	Bill	A
7	Hank	R
8	Will	R
9	Jack	K
10	Jose	K
100	10

If I had 75 entries each letter of "S H A R K" would represent 15 names and so on.... I would prefer this to be a function only, no macros.

Thanks for any help!

[Pete_UK]

Will the number of names always be a multiple of the number of characters in your word? If not, then the last letter will be used a fewer number of times.

Is the work SHARK fixed, or will you want to be able to change that easily?

Pete

[jason.b75]

With 'SHARK' in C1, enter this in B1 and copy down

=MID(C$1,INT((ROWS(A$1:A1)-1)/(COUNTIF(A:A,"> ")/LEN(C$1)))+1,1)

This is based on your example, it will need adjusting if you have column headers.

[Dragonphantom]

Thank you for the speedy response!

Could I request to see that as A1:A99 as a range rather than a column?

As my example is its own sheet, my actual data is in a large data sheet ie Q85:Q184 but not exact as I am currently away from my PC and at work.

[jason.b75]

Sure, I've moved it based on your example range of Q85:Q184, 'SHARK' in S85, this formula in R85 and copied down.

=MID(S$85,INT((ROWS(Q$85:Q85)-1)/(COUNTIF(Q$85:Q$185,"> ")/LEN(S$85)))+1,1)

The range can be larger than needed so you don't need to edit if you add or remove names from the list, it will ignore blanks or numbers (at the bottom of the list, but not the top or middle).

[Dragonphantom]

I look forward to applying this tonight when I get home! Thank you! So just so I understand this doesn't require the use of the total cell at the end of the column?

[jason.b75]

No, the total cell is not used, the COUNTIF part of the formula takes care of that.

One point that would need to be addressed to ensure accuracy was mentioned by Pete_UK in post #2.

Assuming use of the word 'SHARK', how should the letters be allocated if you have (for example) 21,22,23 or 24 names in the list?

[Dragonphantom]

Very good, I'll let you know if I run into any snafu as I try the various data I try to run through.

[jason.b75]

I'll let you know if I run into any snafu as I try the various data I try to run through.

Please do, snafu and fubar frequently inspire new methods and ideas

[Dragonphantom]

Works like a dream! Thank you very much. :D