Need to assign an ID between 1-20 but cannot duplicate any numbers.

We have a license sharing system (allowed to share by the user agreement), that we are currently manually assigning IDs that are not in use, but I want to automate the process. Basically, the function should look at the ID column and assign any number between 1-20 that is not currently in the list.


  • mccoy_FSI
    mccoy_FSI ✭✭✭✭

    Sure, in the below screenshot the license # should be auto-populated and should not be duplicates for dates that overlap. I can solve the overlap date by moving ended periods to another sheet to get rid of them so that's not a problem. In the below example, the program robin will need a license number between 1-20 automatically (they all do I just left it blank to show the step), assigned but it cannot be any of the already listed numbers. It would be a column formula. Basically I need this column to look at the license number database helper sheet, I think, and pull in a license that is not already listed on the main sheet in the license number column. Or maybe that's the wrong way, but any way it is done I cannot figure out.

  • Just a couple more clarification questions:

    1. Is the pool of licenses fixed? I am assuming that the unique license ID's that can be assigned are only from the fixed pool so that the license ID's aren't being generated but are instead from the pool/range. Not critical but how large is the pool/range?
    2. You mention dates, I am assuming that the dates are just when a user is assigned the license (Date Start) and when they finish using the license (Date End). To that effect, the license is just assigned once the Date End is set (personally, I wouldn't use that as a trigger but you could)
  • mccoy_FSI
    mccoy_FSI ✭✭✭✭
    edited 01/20/23

    Yes @Clarkston_Chris that is all correct. A pool of license numbers to assign and yes they use them for a period of time, like three weeks, and then no longer need them so they are open again to be assigned to someone else. We know the dates up front, so the date start and date end are fixed before the license is assigned. There are about 20 license numbers, hence, why I need to assign numbers 1-20.

  • bsikes
    bsikes ✭✭✭

    I was able to get something that seems to accurately calculate the next license number to be used... I just don't know the best way to lock in that value.

    used licenses is a formula that collects all the license # values from all rows that overlap in date with the current row:

    ="-" + JOIN(DISTINCT(COLLECT([license #]:[license #], [date start]:[date start], <=[date start]@row, [date end]:[date end], >=[date start]@row), COLLECT([license #]:[license #], [date end]:[date end], >=[date start]@row, [date start]:[date start], <=[date end]@row)), "-") + "-"

    next license is just a long nested if statement checking if used licenses contains a specific number. If not, that's the license, otherwise check the next number.

    =IF(FIND("-1-", [used licenses]@row) > 0, IF(FIND("-2-", [used licenses]@row) > 0, IF(FIND("-3-", [used licenses]@row) > 0, IF(FIND("-4-", [used licenses]@row) > 0, IF(FIND("-5-", [used licenses]@row) > 0, IF(FIND("-6-", [used licenses]@row) > 0, IF(FIND("-7-", [used licenses]@row) > 0, IF(FIND("-8-", [used licenses]@row) > 0, IF(FIND("-9-", [used licenses]@row) > 0, IF(FIND("-10-", [used licenses]@row) > 0, IF(FIND("-11-", [used licenses]@row) > 0, IF(FIND("-12-", [used licenses]@row) > 0, IF(FIND("-13-", [used licenses]@row) > 0, IF(FIND("-14-", [used licenses]@row) > 0, IF(FIND("-15-", [used licenses]@row) > 0, IF(FIND("-16-", [used licenses]@row) > 0, IF(FIND("-17-", [used licenses]@row) > 0, IF(FIND("-18-", [used licenses]@row) > 0, IF(FIND("-19-", [used licenses]@row) > 0, IF(FIND("-20-", [used licenses]@row) > 0, "No Available Licenses", "20"), "19"), "18"), "17"), "16"), "15"), "14"), "13"), "12"), "11"), "10"), "9"), "8"), "7"), "6"), "5"), "4"), "3"), "2"), "1")

    The problem is finding a good way to record the next license value in the license # column without it being a circular reference or really complicated.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    I would suggest setting up another sheet that will then become the working sheet. Your [Next License] formula would use a cross sheet reference to look at the license number of the new working sheet. The license number in the existing ("Assignment") sheet would be a basic cell reference to the next license column.

    Set up a Move Row automation to trigger when License # changes to any value to push the row over to the new working sheet to capture the static data. The number in the next license column of the Assignment sheet should then update and be ready for the next entry.

  • mccoy_FSI
    mccoy_FSI ✭✭✭✭

    @Paul Newcome I understand what you are saying and that seems like it would work. I am just not sure where to put the cross-sheet reference in the next license formulas working, but need to make the next license formula look at the other sheet as you suggest. Not sure how to do it because the next license formula uses @row, but cross references use entire column references.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!