# Thread: Generating numbers and letters

1. ## Generating numbers and letters

Hello

I want a formula to use in Column B which will generate a number, or a number and a letter combination in Column A, every time I enter a new name or the same name...thus...

Column A Column B

1a Tom
1b Tom
1c Tom
2a John
3a Phil
3b Phil
3c Phil
4a Jeff

Would anyone be able to help?

Thanks so much ;-)

2. What should happen when you get to z?

3. Thanks for your prompt response Ali. To be honest it doesn't really matter, as I don't ever expect to go above l, m, n, o... but I suppose for neatness within the formula, aa, then aaa etc, but if this makes the formula too unwieldy, starting over at a, or ending in z is fine too ;-)

4. Try this:

=SUMPRODUCT(1/COUNTIF(B\$1:B1,B\$1:B1))&CHOOSE(COUNTIF(B\$1:B1,B1),"a","b","c","d","e","f","g","h","I","j","k","i","m","n","o","p","q","r","s","t","u","v","w","x","y","z")

5. Please read the forum policy on cross-posting: http://www.excelguru.ca/content.php?184

6. Hi Ali

I fully appreciate the sentiments here and understand the wisdom of it and am grateful for your time. I posted the question on one other forum which did not seem to be functioning properly and produced no response after 36hrs or so. I was very unclear whether it was a fully functioning forum. Hence the cross-post. I am very much in the same boat as you. I answer questions on other forums in which I have specialist knowledge and feel a bit peeved when my time is taken for granted!

7. Ali

Thank you so much, really appreciated. Just one further detail. I often need to start at a random number, not 1. Is there a simple way of doing this? So effectively in cell A1, I may want 1234a or 1352a etc, with the following cells down the column being 1234b or 1352b etc,

8. Originally Posted by Skinnymarinky
Ali

Thank you so much, really appreciated. Just one further detail. I often need to start at a random number, not 1. Is there a simple way of doing this? So effectively in cell A1, I may want 1234a or 1352a etc, with the following cells down the column being 1234b or 1352b etc,
The formula I have given you determines the number based on a unique count of names. What you now want is quite different, and you should perhaps have made this clear from the outset. Generating a random number and then ensuring it is not repeated with another person will probably be better done with VBA, and I am afraid I am not a VBA expert.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•