With COUNTM I can determine multiple selections are made, how can I extract the specifc selections?

Dale Heincelman

If I have a multiple selection column, like 'assigned to', where I can select some or all of the people, can I determine which specific people from the list have been selected (without using FIND or CONTAINS and looking for names one at time?

Paul Newcome

You will need to parse the names out to be able to reference them in another formula for comparison.

Using one formula in one column would require multiple VLOOKUPS each with their own MID statement using the FIND function as the start and end and also referencing previous MID statements. Then you would have to wrap each of those VLOOKUPS in a SUM or AVG function.

In theory it can be done, but it would be a very complex formula that is very easy to break.

My suggestion would be to create enough additional columns so that each name can be pulled individually or at the very least each VLOOKUP formula result that can then be added together.

Dale Heincelman

Yes you are correct, now looking at the parsing, it would be quite complex using the functions just in one cell tracking how far the text has been parsed. I just wanted to explore the possibility of doing this. Thank you.

Paul Newcome

Happy to help! 👍️ Glad we were able to get your question answered.

Paul Newcome

Can you provide more detail? Are you trying to parse out the multiple selections into their own columns, or are you trying to compare to a set list, or...?

Screenshots of your current setup with manually entered data showing exactly what you want the end result to be would be extremely helpful. Sensitive/confidential data can be blocked, removed, or replaced with "dummy data" as needed.

Dale Heincelman

I would like to parse out only names selected to use in a VLOOKUP for additional attributes associated to the names.

Paul Newcome

Ok. And when you parse out the names, are you going to want them to go across the same row or down the same column?

Same Row:

John, Mike, Steve in the same cell turns to

John Mike Steve in 3 different cells.

Same Column:

John, Mike, Steve in the same cell turns to

John

Mike Steve

in 3 different cells.

Dale Heincelman

What process/function would I use to parse out the names in a multiple selection drop down? Is there a delimiter between names? I would have the list of names in another sheet with the attribute I want to look up if that name is seleceted in the multiple selection drop down.

Paul Newcome

The delimiter in a Multi-select is a line break or CHAR(10). Parsing out the names is possible, but the solutions are different for if you are wanting to parse down a column or across a row.

Would you want the first name in [Column 1]1, second in [Column 2]1, third in [Column 3]1, etc?

Or do you want

First Name in [Column 1]1

Second in [Column 1]2

Third in [Column 1]3

?

Dale Heincelman

I attempted to to use "FIND(CHAR(10),[Assigned to]##) to find the delimiter to use to parse the names out, but the FIND function did not locate CHAR (10). I would use the delimiter to extract each name one at a time based on where the next delimiter occurs. When I Parse each name I will use that to look up (VLOOKUP) a numeric value, from a different sheet, for each name and add them for all names selected.

Paul Newcome

I use the FIND function to locate CHAR(10) for parsing from a multi-select column regularly.

I understand how to parse and how to use the data after it is parsed out. My question is how you want the data displayed once it is parsed.

If you have 3 names in a single cell, do you want those 3 names parsed across one row or parsed down a column?

Dale Heincelman

Did I use the correct syntax for the FIND function?

After parsing each name out I would look up the numeric attribute associated to it from the other sheet, sum each of the attributes for the selected names and display the result of that sum in the adjacent column. What I am doing is looking the rate for each name assigned to a task and creating a combined rate for that task so I could do a ROM cost for that task based on the allocation and duration.

So based on your question "If you have 3 names in a single cell, do you want those 3 names parsed across one row or parsed down a column?" once I look up the rate for the person assigned to the task and their associated rate I no longer care about the name, just the combined rate of all the names selected. So I am not sure about the issue you are asking of a row or column. I will have my formula (when constructed) in a column adjacent to the names selected from the "assigned to" column that will display the combined rate for each task row.

Paul Newcome

You will need to parse the names out to be able to reference them in another formula for comparison.

Using one formula in one column would require multiple VLOOKUPS each with their own MID statement using the FIND function as the start and end and also referencing previous MID statements. Then you would have to wrap each of those VLOOKUPS in a SUM or AVG function.

In theory it can be done, but it would be a very complex formula that is very easy to break.

My suggestion would be to create enough additional columns so that each name can be pulled individually or at the very least each VLOOKUP formula result that can then be added together.

Dale Heincelman

Yes you are correct, now looking at the parsing, it would be quite complex using the functions just in one cell tracking how far the text has been parsed. I just wanted to explore the possibility of doing this. Thank you.

Paul Newcome

Happy to help! 👍️ Glad we were able to get your question answered.

Dale Heincelman

I agree this becomes complex using these techniques. I was hoping there might be other options. Thank you for your insight.

Ann Marie Shields

https://community.smartsheet.com/discussion/comment/236315#Comment_236315

@Paul Newcome I have something similar but I want to parse the data down in the same column. How could I go about doing that?

Paul Newcome

@Ann Marie Shields Could you provide a screenshot of the source data and then a screenshot of manually entered mock data that shows the desired end result?

Ann Marie Shields

@Paul Newcome

Here is what it looks like now.

But I want it to look like this.

I have a main sheet that asks people which states they work in, so we created a multi select so it would be easier on them to just enter information into 1 row. But now, for our purposes we need the states separated out on individual rows.

Hopefully this answers your question and makes sense.

Thank you for responding!!