PDA

View Full Version : Generating numbers and letters

Skinnymarinky
2017-02-04, 10:53 AM
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 ;-)

AliGW
2017-02-04, 10:55 AM
What should happen when you get to z?

Skinnymarinky
2017-02-04, 11:15 AM
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 ;-)

AliGW
2017-02-04, 11:33 AM
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")

AliGW
2017-02-04, 11:41 AM

Skinnymarinky
2017-02-05, 12:36 PM
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,

Skinnymarinky
2017-02-05, 12:42 PM
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!

AliGW
2017-02-05, 02:04 PM
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.

Skinnymarinky
2017-02-05, 09:01 PM
Ali

Understood, thank you for your time.