Formula Help!

System

This discussion was created from comments split from: Join Collect formula for multiple values.

FrogHolder_01

Howdy—I have a similar problem I'm trying to solve, where a formula looks up information about a live organism (species, name etc) from a different sheet using "OrganismID". The big difference is that this column is not a true multi-select column, just a text field, where users are entering the IDs manually, separated by carriage returns. This format is necessary for two reason: 1) The drop-down list would be godawful long, and difficult to maintain as animals…well…die; 2) When you enter drop-down values Smartsheet automatically sorts them, and that's problematic as well, the user is entering some other data that needs to be "lined up" with the OrganismIDs, and doesn't always enter the latter in numerical order. I have a series of automations that parses out this data into a separate sheet, one row per OrganismID/related data.

While the OrganismIDs are unique, the problem here is that my Join/Collect formula is picking up partial values. In the example below, the {ID} of the Madagascar Hissing Cockroach is 1036, so it's being pulled in as a match for OrganismID 10366.

My understanding is that HAS would be a better solution here than COLLECT, since it finds exact matches, but I can't figure out how to make it work in this unusual context, or if it even would. Any suggestions?

Paul Newcome

You would use the HAS in place of the CONTAINS actually.

But you can leave the manual entry column as is and then insert a multi-select dropdown column as a helper column (that can be hidden after setting up if need be). Using a basic cell reference would provide for the text/number column to be pulled in as if they were multiple selections made in the dropdown column since users are using the carriage return as a delimiter.

=OrganismID@row

Then change your CONTAINS to a HAS and reference the helper column instead of the original column.

FrogHolder_01

@Paul N , thank you for the fast reply (slightly awed here…) I tried that solution, but unfortunately, it does sort the helper field numerically:

The issue here is that when our staff enter OrganismID, in a second column they're also entering a ranking for how the animal did on the program. They're trained to enter both the ID and ranking in the same order, and that data is parsed out in that same order. So "Rosy Boa" would rank "behaved normally" and "Glass Lizard" would rank "Bit me on the Nose".

With the automatic sort of the helper column, the OrganismIDs reverse in this case, and the poor Rosy Boa would get all the blame for nose-biting (Science Museum Problems). But if worst comes to worst, I can punt this formula farther down the line, after the related data is parsed, that will work fine. I'm just trying to up my SS Game and wanted to see if there was a first-step solution I missed. Grateful if you have any other ideas.

Paul Newcome

I would suggest putting this further down the line after parsing.