How to divide single request with multiple contacts to individual requests.

Leslie F_ MHM

Does anyone know how to take a single line, with a single multi-select cell, and have that line parse/delineate/divide in to individual lines, either through a formula or an automation?

Scenario: we are receiving schedule block request for team, and multiple people may be on the block request, therefore we have multiple names on a single sheet line; the scheduling team wants to confirm they have blocked each individual person by each person having their own line on the sheet.

Is there away to go from the top line to the individual 3 lines, either on another sheet or through a formula?

Paul Newcome

How are new rows being added? If you are using form submissions, we can use a separate sheet, some formulas, and a copy row automation to accomplish this.

Leslie F_ MHM

Hi there- yes rows are added through a form that leaders complete.

Leslie F_ MHM

@Paul Newcome - yes rows are added through a form that leaders complete. - (Didn't know I could at you in my first response! )

Paul Newcome

Excellent. And what is the maximum number of options someone would potentially select in the multi-select dropdown?

Leslie F_ MHM

@Paul Newcome leaders would ideally be able to set up a meeting request for all their team members, which is an unknown number. It is ok if there is a cap that is set by the form to help the process. Some of the largest teams are about 20-25 people.

Paul Newcome

We can set it for as many as you need. We just need to make sure we set it up to cover enough. The only real "cap" is going to be no more than 4,000 characters in a cell including spaces and line breaks.

If you say the largest teams are 20 - 25 people, we can go ahead and build it for up to 40 just in case. Does that sound good to you?

Leslie F_ MHM

@Paul Newcome Yes, that sounds great!

Leslie F_ MHM

@Paul Newcome Hi Paul, Any feedback on how I start this process of mapping out this workflow/automation/formulas?

Paul Newcome

My apologies for the delay. Things got a little crazy on me. We are going to be using the below linked solution as the base idea. You are only going to have one "Option Column", and the formulas in section 2 will be different (to be provided as soon as I can get a chance to write them out). In the meant time, you can go ahead and start working on getting the rest of it set up to include the formatting section, second sheet, and copy row automation.

https://community.smartsheet.com/discussion/119380/split-multiple-selections-in-single-form-into-individual-rows-core-product

Paul Newcome

The modification to the above will be as follows:

Insert a text/number column called "Number". Manually enter the numbers 1 through however many you would need. In this case it would be 1 - 40. These numbers will go in the copy section where we have the [Copy Section] boxes checked.

You will then put the below formula into the [Option] column on the row where you manually entered 1 in the last step.

=IFERROR(MID("!" + SUBSTITUTE(INDEX([Multi-Select]:[Multi-Select], 1), CHAR(10), "!") + "!", FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Multi-Select]:[Multi-Select], 1), CHAR(10), "!") + "!", "!", "~", Number@row)) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Multi-Select]:[Multi-Select], 1), CHAR(10), "!") + "!", "!", "~", Number@row + 1)) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Multi-Select]:[Multi-Select], 1), CHAR(10), "!") + "!", "!", "~", Number@row)) + 1)), "")

From there you would dragfill it down the rows until you have filled all 40 rows.

Leslie F_ MHM

@Paul Newcome WOW! thank you so much, I will start working on this solution! Thank you so much!

Paul Newcome

Please don't hesitate to let me know if you need clarification or anything. I tried to be as clear as possible with the walkthroughs, but sometimes I miss documenting steps.

Leslie F_ MHM

@Paul Newcome Hello there! I am struggling with some of the formula processing. I have the automations to push lines back and forth between the sheets and the formula is in our sheets to parse out the multi select cell to the option cell, but as new lines come in the formula is not running on the new lines.

The formula is connected to the full column, but it is just blank.

Any ideas?

Paul Newcome

@Leslie F_ MHM Are you able to provide screenshots?

Leslie F_ MHM

@Paul Newcome Hi Paul! Sorry I missed your last message - The new form submission comes in at the bottom of the sheet - and for some reason this impacts the parsing.