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

Mike Tomei

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.

KPH

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 + "; "

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

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

KPH

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

Is there a maximum quantity?

Mike Tomei

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

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

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 + "; "