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

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?

Best Answers

  • Dale Heincelman
    Dale Heincelman ✭✭✭✭✭
    Answer ✓

    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.

«1

Answers

  • Paul Newcome
    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
    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
    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
    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
    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
    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
    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
    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.

  • Dale Heincelman
    Dale Heincelman ✭✭✭✭✭
    Answer ✓

    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.

  • Dale Heincelman
    Dale Heincelman ✭✭✭✭✭

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

  • @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
    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?

  • @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!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!