How can I create a variation of a JOIN formula to create IDs based on quantity?

Looking at my attached image, I'm looking to use a variation of a JOIN formula to create the Unique IDs that appear in the last column. Depending on quantity, a Unique ID would be the Device Type and an incrementally increasing number (starting with "1"). The Unique ID column formatting doesn't have to exactly match what I'm showing. Is this possible? Thanks.

Best Answer

  • KPH
    KPH ✭✭✭✭✭✭
    edited 02/06/24 Answer ✓

    Community is very slow to post comments today. 😕 Your response came in 15 minutes after I sent the answer for 5. My method doesn't seem practical for 30. That is a lot of formula.

    It does work though:

    But I think there is a better way.... Time to rethink, but I need to log off for today.

    Here it is for the time being though (to save you doing all the typing I just did to test it, if you want it):

    =  IF(Quantity@row - 30 > 0, [Device Type]@row + (Quantity@row - 30) + "; ", "")
    + IF(Quantity@row - 29 > 0, [Device Type]@row + (Quantity@row - 29) + "; ", "")
    + IF(Quantity@row - 28 > 0, [Device Type]@row + (Quantity@row - 28) + "; ", "")
    + IF(Quantity@row - 27 > 0, [Device Type]@row + (Quantity@row - 27) + "; ", "")
    + IF(Quantity@row - 26 > 0, [Device Type]@row + (Quantity@row - 26) + "; ", "")
    + IF(Quantity@row - 25 > 0, [Device Type]@row + (Quantity@row - 25) + "; ", "")
    + IF(Quantity@row - 24 > 0, [Device Type]@row + (Quantity@row - 24) + "; ", "")
    + IF(Quantity@row - 23 > 0, [Device Type]@row + (Quantity@row - 23) + "; ", "")
    + IF(Quantity@row - 22 > 0, [Device Type]@row + (Quantity@row - 22) + "; ", "")
    + IF(Quantity@row - 21 > 0, [Device Type]@row + (Quantity@row - 21) + "; ", "")
    + IF(Quantity@row - 20 > 0, [Device Type]@row + (Quantity@row - 20) + "; ", "")
    + IF(Quantity@row - 19 > 0, [Device Type]@row + (Quantity@row - 19) + "; ", "")
    + IF(Quantity@row - 18 > 0, [Device Type]@row + (Quantity@row - 18) + "; ", "")
    + IF(Quantity@row - 17 > 0, [Device Type]@row + (Quantity@row - 17) + "; ", "")
    + IF(Quantity@row - 16 > 0, [Device Type]@row + (Quantity@row - 16) + "; ", "")
    + IF(Quantity@row - 15 > 0, [Device Type]@row + (Quantity@row - 15) + "; ", "")
    + IF(Quantity@row - 14 > 0, [Device Type]@row + (Quantity@row - 14) + "; ", "")
    + IF(Quantity@row - 13 > 0, [Device Type]@row + (Quantity@row - 13) + "; ", "")
    + IF(Quantity@row - 12 > 0, [Device Type]@row + (Quantity@row - 12) + "; ", "")
    + IF(Quantity@row - 11 > 0, [Device Type]@row + (Quantity@row - 11) + "; ", "")
    + IF(Quantity@row - 10 > 0, [Device Type]@row + (Quantity@row - 10) + "; ", "")
    + IF(Quantity@row - 9 > 0, [Device Type]@row + (Quantity@row - 9) + "; ", "")
    + IF(Quantity@row - 8 > 0, [Device Type]@row + (Quantity@row - 8) + "; ", "")
    + IF(Quantity@row - 7 > 0, [Device Type]@row + (Quantity@row - 7) + "; ", "")
    + IF(Quantity@row - 6 > 0, [Device Type]@row + (Quantity@row - 6) + "; ", "")
    + IF(Quantity@row - 5 > 0, [Device Type]@row + (Quantity@row - 5) + "; ", "")
    + IF(Quantity@row - 4 > 0, [Device Type]@row + (Quantity@row - 4) + "; ", "")
    + IF(Quantity@row - 3 > 0, [Device Type]@row + (Quantity@row - 3) + "; ", "")
    + IF(Quantity@row - 2 > 0, [Device Type]@row + (Quantity@row - 2) + "; ", "")
    + IF(Quantity@row - 1 > 0, [Device Type]@row + (Quantity@row - 1) + "; ", "")
    + [Device Type]@row + Quantity@row + "; "
    

Answers

  • KPH
    KPH ✭✭✭✭✭✭

    Can you show what would happen if another row was added, say SPEAKER with a Quantity of 3. Would the Unique IDs be SPEALER5; SPEAKER6; SPEAKER7?

    How many different device types are there?

  • Mike Tomei
    Mike Tomei ✭✭✭

    Great question @KPH . There would only be one row of "SPEAKER". Subsequent rows might be totally different Device Type names. Thanks!

  • KPH
    KPH ✭✭✭✭✭✭

    Oh OK, you're not doing what I thought but that's actually easier. 😅

    Is there a maximum quantity?

  • Mike Tomei
    Mike Tomei ✭✭✭

    Hi @KPH . I would say that quantity can vary from 1 up to 30.

  • KPH
    KPH ✭✭✭✭✭✭

    OK, here is hoping the maximum quantity is a low number, like 4, and not 4000 (or even 40). If so, you can do something like this:

    = IF(Quantity@row - 3 > 0, [Device Type]@row + (Quantity@row - 3) + "; ", "")

    + IF(Quantity@row - 2 > 0, [Device Type]@row + (Quantity@row - 2) + "; ", "")

    + IF(Quantity@row - 1 > 0, [Device Type]@row + (Quantity@row - 1) + "; ", "")

    + [Device Type]@row + Quantity@row + "; "

    To create this:

    You are basically saying if the quantity minus 3 is more than 0 put in the device type and then the quantity minus three and then a semi colon. If the quantity minus 3 is 0 or less then don't do anything.

    Next row, if the quantity minus 2 is more than 0 put in the device type and then the quantity minus 2 and then a semi colon. If the quantity minus 2 is 0 or less then don't do anything.

    And you've probably guessed it, next, if the quantity minus 1 is more than 0 put in the device type and then the quantity minus 1 and then a semi colon. If the quantity minus 1 is 0 or less then don't do anything.

    Then you finish with the device type and the quantity and a semi-colon.

    This works for up to 4. To extend it to 5 you add another block at the start:

    = IF(Quantity@row - 4 > 0, [Device Type]@row + (Quantity@row - 4) + "; ", "")

    + IF(Quantity@row - 3 > 0, [Device Type]@row + (Quantity@row - 3) + "; ", "")

    + IF(Quantity@row - 2 > 0, [Device Type]@row + (Quantity@row - 2) + "; ", "")

    + IF(Quantity@row - 1 > 0, [Device Type]@row + (Quantity@row - 1) + "; ", "")

    + [Device Type]@row + Quantity@row + "; "

    Obviously, if you have many more than this, it is not going to work and we'll need a new plan. But I'm hopeful.

    🤞 🤞 🤞 🤞 🤞 🤞 🤞

    

  • KPH
    KPH ✭✭✭✭✭✭
    edited 02/06/24 Answer ✓

    Community is very slow to post comments today. 😕 Your response came in 15 minutes after I sent the answer for 5. My method doesn't seem practical for 30. That is a lot of formula.

    It does work though:

    But I think there is a better way.... Time to rethink, but I need to log off for today.

    Here it is for the time being though (to save you doing all the typing I just did to test it, if you want it):

    =  IF(Quantity@row - 30 > 0, [Device Type]@row + (Quantity@row - 30) + "; ", "")
    + IF(Quantity@row - 29 > 0, [Device Type]@row + (Quantity@row - 29) + "; ", "")
    + IF(Quantity@row - 28 > 0, [Device Type]@row + (Quantity@row - 28) + "; ", "")
    + IF(Quantity@row - 27 > 0, [Device Type]@row + (Quantity@row - 27) + "; ", "")
    + IF(Quantity@row - 26 > 0, [Device Type]@row + (Quantity@row - 26) + "; ", "")
    + IF(Quantity@row - 25 > 0, [Device Type]@row + (Quantity@row - 25) + "; ", "")
    + IF(Quantity@row - 24 > 0, [Device Type]@row + (Quantity@row - 24) + "; ", "")
    + IF(Quantity@row - 23 > 0, [Device Type]@row + (Quantity@row - 23) + "; ", "")
    + IF(Quantity@row - 22 > 0, [Device Type]@row + (Quantity@row - 22) + "; ", "")
    + IF(Quantity@row - 21 > 0, [Device Type]@row + (Quantity@row - 21) + "; ", "")
    + IF(Quantity@row - 20 > 0, [Device Type]@row + (Quantity@row - 20) + "; ", "")
    + IF(Quantity@row - 19 > 0, [Device Type]@row + (Quantity@row - 19) + "; ", "")
    + IF(Quantity@row - 18 > 0, [Device Type]@row + (Quantity@row - 18) + "; ", "")
    + IF(Quantity@row - 17 > 0, [Device Type]@row + (Quantity@row - 17) + "; ", "")
    + IF(Quantity@row - 16 > 0, [Device Type]@row + (Quantity@row - 16) + "; ", "")
    + IF(Quantity@row - 15 > 0, [Device Type]@row + (Quantity@row - 15) + "; ", "")
    + IF(Quantity@row - 14 > 0, [Device Type]@row + (Quantity@row - 14) + "; ", "")
    + IF(Quantity@row - 13 > 0, [Device Type]@row + (Quantity@row - 13) + "; ", "")
    + IF(Quantity@row - 12 > 0, [Device Type]@row + (Quantity@row - 12) + "; ", "")
    + IF(Quantity@row - 11 > 0, [Device Type]@row + (Quantity@row - 11) + "; ", "")
    + IF(Quantity@row - 10 > 0, [Device Type]@row + (Quantity@row - 10) + "; ", "")
    + IF(Quantity@row - 9 > 0, [Device Type]@row + (Quantity@row - 9) + "; ", "")
    + IF(Quantity@row - 8 > 0, [Device Type]@row + (Quantity@row - 8) + "; ", "")
    + IF(Quantity@row - 7 > 0, [Device Type]@row + (Quantity@row - 7) + "; ", "")
    + IF(Quantity@row - 6 > 0, [Device Type]@row + (Quantity@row - 6) + "; ", "")
    + IF(Quantity@row - 5 > 0, [Device Type]@row + (Quantity@row - 5) + "; ", "")
    + IF(Quantity@row - 4 > 0, [Device Type]@row + (Quantity@row - 4) + "; ", "")
    + IF(Quantity@row - 3 > 0, [Device Type]@row + (Quantity@row - 3) + "; ", "")
    + IF(Quantity@row - 2 > 0, [Device Type]@row + (Quantity@row - 2) + "; ", "")
    + IF(Quantity@row - 1 > 0, [Device Type]@row + (Quantity@row - 1) + "; ", "")
    + [Device Type]@row + Quantity@row + "; "
    

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!