Results 1 to 9 of 9

Thread: Generating numbers and letters

  1. #1

    Generating numbers and letters



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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. #2
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,061
    Articles
    0
    Excel Version
    Office 365 Subscription
    What should happen when you get to z?
    Ali
    Enthusiastic self-taught user of MS Excel!

  3. #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. #4
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,061
    Articles
    0
    Excel Version
    Office 365 Subscription
    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
    Enthusiastic self-taught user of MS Excel!

  5. #5
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,061
    Articles
    0
    Excel Version
    Office 365 Subscription
    Please read the forum policy on cross-posting: http://www.excelguru.ca/content.php?184
    Ali
    Enthusiastic self-taught user of MS Excel!

  6. #6
    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,

  7. #7
    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!

  8. #8
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,061
    Articles
    0
    Excel Version
    Office 365 Subscription
    Quote Originally Posted by Skinnymarinky View Post
    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.
    Ali
    Enthusiastic self-taught user of MS Excel!

  9. #9
    Ali

    Understood, thank you for your time.

Posting Permissions

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