Generating numbers and letters

Skinnymarinky

New member
Joined
Feb 4, 2017
Messages
5
Reaction score
0
Points
0
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 ;-)
 
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 ;-)
 
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")
 
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,
 
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!
 
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.
 
Back
Top