+ Reply to Thread
Results 1 to 10 of 10

Google Sheets - Generating a Sequential list based on previous values

  1. #1
    Registered User
    Join Date
    05-14-2018
    Location
    Singapore
    MS-Off Ver
    Office 365
    Posts
    5

    Google Sheets - Generating a Sequential list based on previous values

    Hi,

    I am trying to generate a unique ID in Google Sheets.

    The structure of the unique ID is the following:

    2x Letter values (A-Z)+ 2x Digit values (18,19 or 20) + 2x Alphanumeric (A1,A2...Z9).

    With the above, an example unique ID will look like this: GB18A1

    I would like the last 2 alphanumeric characters to be generated in sequence (A1, A2.. A9, B1, B2.. B9...) and taking into account previous 4 characters, since the sequence is dependent on the first 4 characters and does not run independently.

    So, when someone is creating a new ID with first 4 characters being LD18, sequence will start from A1. Next time some creates an ID with LD18 as first 4 characters, A2 will be spit out. But if first 4 characters are changed, sequence will have to start from A1 again.

    I am unable to post a link but will do so if it's required (my forum access rights do not allow me to post links yet)

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    Re: Google Sheets - Generating a Sequential list based on previous values

    Quote Originally Posted by lalalalalala View Post
    ... (my forum access rights do not allow me to post links yet) ...
    But you can attach a workbook - see the yellow banner at the top of the screen for details of how to do this.

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    05-14-2018
    Location
    Singapore
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Google Sheets - Generating a Sequential list based on previous values

    Thank you Pete. Attached the sheet.
    Attached Files Attached Files

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    Re: Google Sheets - Generating a Sequential list based on previous values

    I'm just about to log out now and go to bed, as it is past 2:15 am here. I'm out for most of tomorrow as well, so I'll have to pick this up in the evening, unless someone else can chip in.

    Pete

  5. #5
    Registered User
    Join Date
    05-14-2018
    Location
    Singapore
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Google Sheets - Generating a Sequential list based on previous values

    I managed to get alphanumeric sequence working but independent of previous values. I am still looking for a solution that would run the sequence based on past values.

    example:

    AB12A1
    AB12A2
    AB12A3
    QB13A1
    QB13A2

  6. #6
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    Re: Google Sheets - Generating a Sequential list based on previous values

    It would help if you had a few more rows of data than one, and you can show how you expect the ID to change each time.

    Pete

  7. #7
    Registered User
    Join Date
    05-14-2018
    Location
    Singapore
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Google Sheets - Generating a Sequential list based on previous values

    you're right. Here comes the updated sheet with a few more examples
    Attached Files Attached Files

  8. #8
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    Re: Google Sheets - Generating a Sequential list based on previous values

    See if this does what you want. Put this formula in D2:

    =CHAR(INT(COUNTIF(E$1:E1,A2&B2&C2&"*")/9)+65)&MOD(COUNTIF(E$1:E1,A2&B2&C2&"*"),9)+1

    then copy down. Note that it depends on column E also being completed, so you should copy both formulae down further if you add some more data.

    Hope this helps.

    Pete

  9. #9
    Registered User
    Join Date
    05-14-2018
    Location
    Singapore
    MS-Off Ver
    Office 365
    Posts
    5

    Re: Google Sheets - Generating a Sequential list based on previous values

    This is impressive, it worked. Thank you Pete!

  10. #10
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    22,784

    Re: Google Sheets - Generating a Sequential list based on previous values

    You're welcome - glad to help.

    If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

    You might also like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

    Pete

+ 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. Google Apps Script for Google Sheets Pulling Formulas from Master to Several Slave Sheets
    By excelroofing in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 4
    Last Post: 08-22-2018, 02:06 AM
  2. Generating sequential unique ID based on other value and date
    By Gallant Birch in forum Excel General
    Replies: 1
    Last Post: 08-27-2016, 02:58 PM
  3. email row contents based on cell values (google sheets populated by google forms)
    By reedg in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 0
    Last Post: 01-13-2016, 02:55 PM
  4. [SOLVED] Running total from previous sheets with non-sequential names?
    By emillerd in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-28-2014, 04:57 PM
  5. Generating a list of unique values based on specific conditions
    By Gobaith in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-05-2012, 11:35 AM
  6. Generating the next number automatically based on a previous cell value
    By hpfeiffer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 06-04-2012, 03:04 AM
  7. Generating list of sequential numbers (sans formulas)
    By gregorybrian in forum Excel General
    Replies: 2
    Last Post: 06-21-2009, 11:22 AM

Tags for this Thread

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