+ Reply to Thread
Results 1 to 12 of 12

Sequential Alphanumeric entries

  1. #1
    Registered User
    Join Date
    06-19-2014
    Location
    Shrewsbury, England
    MS-Off Ver
    2010
    Posts
    5

    Sequential Alphanumeric entries

    Hi, I'm new to the forum, as I'm also quite new to Excel and although variations of this topic have been covered in several other threads, none of them seem to answer my query - unless I just can't find it..

    I'm trying to create a single column of stock bin codes for eventual conversion to barcodes. The bin codes run like this:
    A1A
    A1B
    A1C - etc. to A1H, then the sequence starts again with:
    A2A
    A2B
    A2C etc. up to A6H, when the 1st character changes to B and the sequence starts again

    This needs to continue until the sequence reaches Z6H

    Can anyone please advise me in simple terms how to do this without having to enter each code manually?

    Thanks in advance
    Last edited by Tammistar; 06-19-2014 at 11:20 AM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Sequential Alphanumeric entries

    One way, with VBA:

    Please Login or Register  to view this content.

    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Sequential Alphanumeric entries

    The following code will generate the codes in column A starting at row 10
    Please Login or Register  to view this content.

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Sequential Alphanumeric entries

    Alternative, formula-based solution:

    ="A"&1+INT((ROWS($1:1)-1)/8)&CHAR(65+MOD(ROWS($1:1)-1,8))

    Copy down as required.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: Sequential Alphanumeric entries

    Obviously XOR LX's solution is a lot shorter but seeing as I spent a while working it out:
    =MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",INT(ROW(A1)/48)+1,1) & IF(RIGHT(A1,1)="H",MAX(1,MOD(--MID(A1,2,1)+1,7)),MID(A1,2,1)) & MID("HABCDEFG",MOD(FIND(RIGHT(A1,1),"ABCDEFGH")+1,8)+1,1)

    in A2 with "A1A" type in A1 and copy down

  6. #6
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Sequential Alphanumeric entries

    Well I worked on this, I might as well posti it

    from row 1
    =CHAR(97+INT(MOD((ROW()-1)/48,48)))&CHAR(49+INT(MOD((ROW()-1)/8,6)))&CHAR(97+MOD(ROW()-1,8))
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  7. #7
    Registered User
    Join Date
    06-19-2014
    Location
    Shrewsbury, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Sequential Alphanumeric entries

    This almost works but I need the first character, in this case A to change to B after the 2nd character has reached 6. So the next code after A6H would be B1A, then again through to B6H, when the code will change to C1A and so on. Sorry if I'm not making it too clear but thanks for your help.

  8. #8
    Registered User
    Join Date
    06-19-2014
    Location
    Shrewsbury, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Sequential Alphanumeric entries

    This is way over my head but thanks for your response

  9. #9
    Registered User
    Join Date
    06-19-2014
    Location
    Shrewsbury, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Sequential Alphanumeric entries

    Fantastic, that's done the trick, thanks very much

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Sequential Alphanumeric entries

    Not entirely sure which posters you were responding to and which solution(s) worked.

    However, you're welcome


    If you are satisfied with the solution(s) provided, please mark your thread as Solved.


    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:

    How to mark a thread Solved
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word Title you will see a dropdown with the word No prefix.
    Change to Solved
    Click Save


    You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

  11. #11
    Registered User
    Join Date
    06-19-2014
    Location
    Shrewsbury, England
    MS-Off Ver
    2010
    Posts
    5

    Re: Sequential Alphanumeric entries

    Sorry, still trying to meet a deadline so didn't notice the replies were generic and not linked to individual posts.

    Thanks to everyone who answered, I'm blown away with the speed you guys replied to this. I used Special-K's formula in the end.

  12. #12
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,086

    Re: Sequential Alphanumeric entries

    OK, good. If you need that in Upper Case, as in your example:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Run-time error '1004' with vba for sequential auto-number for database entries
    By Orestees in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 09-20-2012, 10:50 AM
  2. Replies: 4
    Last Post: 07-29-2012, 08:40 AM
  3. Prefixing Alphanumeric entries copied from another sheet
    By MickyWykes in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-25-2012, 08:52 AM
  4. [SOLVED] Finding unique entries among two columns of alphanumeric data
    By Bob in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-20-2006, 09:10 PM
  5. how can I sort alphanumeric entries by number in excel
    By beasleyb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-27-2005, 06:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1