(RANKEQ) Derivative Numbering System e.g. 1a, 1b, 2a, 2b, 2c etc.
Hello, I’m a bit stuck with creating a numbering system – can anyone help please?
I have a list of IDs, but some of these IDs I want to mark as derivatives of others and automatically label the derivatives with a letter ideally – but a number will do.
I need my formula to look down a column, count distinct numbers and where [Derivative?] is True assign a rank within that range of distinct numbers.
So the first item in that ‘group’ of distinct numbers that is checked True will be 1 (or a), the second in that group would be 2 (or b) and so on. From there I can relate the number to a letter and concatenate the results to give me my output Item ID.
I have another row ID column that is autonumbered that could drive the ranking.
Or am I overthinking this?
Hope someone can help.
Many thanks,
Jim
Best Answers

@Jim give this a try:
=[Item ID]@row + IFERROR(CHAR(96 + RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Item ID]:[Item ID], [Item ID]@row, [derivative?]:[derivative?], 1), 1)), "")

The COLLECT function is one of my absolute favorites! In this formula, first it finds the set of row ID #s that have the same Item ID, are checked off as derivative, to know which one to rank.
Then I'm taking advantage of the CHAR function https://help.smartsheet.com/function/char and the fact that the character number for a lower case "a" is 97. So if rank = 1, it does 96+1 to know that it's an "a". This will fail if you have more than 26 derivatives, so you might need to modify in that case.

Not sure if I'm following correctly, but I think maybe a solution is moving that logic into the formula to prevent the circle?
=[1]@row + [2]@row + [3]@row + IFERROR(CHAR(96 + RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [1]:[1], [1]@row, [2]:[2], [2]@row, [3]:[3], [3]@row, [derivative?]:[derivative?], 1), 1)), "")
Or another might be having an intermediate "helper" column then the ultimate output of ITEM ID.
Answers

@Jim give this a try:
=[Item ID]@row + IFERROR(CHAR(96 + RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [Item ID]:[Item ID], [Item ID]@row, [derivative?]:[derivative?], 1), 1)), "")

Perfect, Katie. Thank you so much.
Now to figure out how you did it!
All the best,
Jim

The COLLECT function is one of my absolute favorites! In this formula, first it finds the set of row ID #s that have the same Item ID, are checked off as derivative, to know which one to rank.
Then I'm taking advantage of the CHAR function https://help.smartsheet.com/function/char and the fact that the character number for a lower case "a" is 97. So if rank = 1, it does 96+1 to know that it's an "a". This will fail if you have more than 26 derivatives, so you might need to modify in that case.

That is so brilliant. Thank you, Katie. Jim

I fixed it. Phew.
sorry @Katie G, me again. I ran into a problem here with a circular reference.
Didn't see this coming so I did not think to mention that the [Item ID] is a concatenation of 3 other columns and if [Derivative?] is checked I want to used the [Concatenated Output] rather than [1]+[2]+[3].
This means the IF formula in the [Item ID] column cannot run because it is referring to itself.
Is there a workaround here?
Thanks,
Jim

Not sure if I'm following correctly, but I think maybe a solution is moving that logic into the formula to prevent the circle?
=[1]@row + [2]@row + [3]@row + IFERROR(CHAR(96 + RANKEQ([Row ID]@row, COLLECT([Row ID]:[Row ID], [1]:[1], [1]@row, [2]:[2], [2]@row, [3]:[3], [3]@row, [derivative?]:[derivative?], 1), 1)), "")
Or another might be having an intermediate "helper" column then the ultimate output of ITEM ID.

Thank you for your help, Katie.
Jim
Help Article Resources
Categories
Check out the Formula Handbook template!