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
-
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
-
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?
-
Great question @KPH . There would only be one row of "SPEAKER". Subsequent rows might be totally different Device Type names. Thanks!
-
Oh OK, you're not doing what I thought but that's actually easier. 😅
Is there a maximum quantity?
-
Hi @KPH . I would say that quantity can vary from 1 up to 30.
-
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.
🤞 🤞 🤞 🤞 🤞 🤞 🤞
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!