Generating numbers and letters

[Skinnymarinky]

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]

What should happen when you get to z?

[Skinnymarinky]

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]

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]

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

[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,

[Skinnymarinky]

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]

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]

Ali

Understood, thank you for your time.