Split Multiple Selections In Single Form Into Individual Rows (Core Product)

2

Comments

  • chris.wang
    chris.wang ✭
    edited 08/01/24

    That would be the

    =IF(Option@row <> "", MAX([Submission #]:[Submission #]))

    formula you had provided. (Total newbie to Smartsheets) I am trying to align column headers as much as I can with your example! @Paul Newcome

    · Share on FacebookShare on Twitter
  • chris.wang
    chris.wang ✭
    https://community.smartsheet.com/discussion/comment/447347#Comment_447347

    Hey @Paul Newcome, sorry about the delay - my reply did not go through. I had copied your formula -

    [Submission Number] =IF(Option@row <> "", MAX([Submission #]:[Submission #]))

    [Option] =IFERROR(MID("!" + SUBSTITUTE(INDEX([Category - Industry of work?]@row, 1), CHAR(10), "!") + "!", FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Category - Industry of work?]@row, 1), CHAR(10), "!") + "!", "!", "~", Number@row)) + 1, FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Category - Industry of work?]@row, 1), CHAR(10), "!") + "!", "!", "~", Number@row + 1)) - (FIND("~", SUBSTITUTE("!" + SUBSTITUTE(INDEX([Category - Industry of work?]@row, 1), CHAR(10), "!") + "!", "!", "~", Number@row)) + 1)), "")

    [Name] =IF(Option@row <> "", INDEX(Key@row, 1))

    This is how it's looking now. (This is only on the first sheet where Forms come in, I have not connected it to the second sheet yet)

    image.png

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @chris.wang What is in the [Submission #] column? This should be an auto-number type column.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • chris.wang
    chris.wang ✭

    Hey @Paul Newcome,

    I was reviewing through the other comments and noticed that most of the examples have multiselects options of only a few options such as A-E, 1-5. Is this still an applicable solution for a form that has a question that has over 400 multiresponse options?

    Thanks -

    · Share on FacebookShare on Twitter
  • chris.wang
    chris.wang ✭

    Hey @Paul Newcome just wanted to follow up on my last comment!

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @chris.wang I haven't had any issues with scaling thus far, but I also have not had to scale that far. The best I can tell you is to give it a try and see how it works.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Kate H
    Kate H ✭✭
    edited 09/23/24

    Hey @Paul Newcome this process is awesome and exactly what I needed! I am having trouble utilizing this for more than one row. The way I have this setup, it works for Multi Select Test 3 but doesn't register Test 2 and Test 1. Any idea why it doesn't register these? Do you have to hand update the formula in the [Option] and [Name] columns to create rows for each possibility? Like should I have the first 5 reference …Test 3 and the next 5 change to reference …Test 2?

    This is Form Sheet: I really am just focused on getting the Form Name and Multi Select Columns to create the new rows on Final Sheet, one for each multi selected option.

    image.png

    Final Sheet seems to look fine. It copies over what it should.

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Kate H This particular setup will only show the newest row on that sheet. It is designed to copy the bottom section over to the target sheet as new rows are added through a form (meaning new rows added one at a time). The bottom section is not designed to show all row all at the same time.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Kate H
    Kate H ✭✭

    @Paul Newcome thanks for the response. Is there anyway to add or change it to make it see all?

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    That would be the second sheet in this solution. The sheet that is the recipient of the copy row automation from this sheet.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • Kate H
    Kate H ✭✭

    @Paul Newcome yes I can make the final sheet work fine but I'm not creating inputs to these formulas in the same way - with a form one at a time. I'd like to have the person copy paste from 30-150 lines into a sheet, select the multi select options to denote the groups impacted, then this process create a row for each multiselect for all 30-150 rows added in the form sheet without having to use a form. If it's a predefined area, say just create 150 cells in a specific column, can you help me make a loop that instead of looking at the newest one, that it looks at all 150 and processes them?

    · Share on FacebookShare on Twitter
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    You would have to recreate the Copy Section that many times going down the sheet. You would then adjust the formula that pulls in the MAX to use a LARGE function instead. The first Copy Section would be 1. The second Copy Section would be 2, so on and so forth.

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

    · Share on FacebookShare on Twitter
  • NeilKY
    NeilKY ✭✭
    https://community.smartsheet.com/discussion/comment/455099#Comment_455099

    @Kate H , did you get this up and working? If so, i would LOOOVE to have a copy of your template. I'm trying to do the same thing but man it's hard to follow these posts for me as a newbie.

    · Share on FacebookShare on Twitter
  • NeilKY
    NeilKY ✭✭

    @Paul Newcome I'm having an issue with copying only the data wanted to the new sheet. I'm not quite sure why. Here are screenshots of the data, using multi-select. Maybe you can tell from this?

    Before submitting the form:

    {2E3EB506-4F2B-42E3-BB59-96E7B906AF9F}.png

    Formulas on this sheet:

    Number: (plain text, manually input numbers)

    Option: (Rows 4-13 are the same) =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)), "")

    Name: (Plain text, "Multi-Select Test 3" for all rows with text)

    Submission Number: Column Formula of: =IF(Option@row <> "", MAX([Submission #]:[Submission #]))

    Submission #: Autonumber Column Format

    Form Name: Plain Text

    Multi-Select: Multi-Select format

    Here's the workflow:

    {2702114B-713E-46E7-8128-418B2D386BAA}.png

    Here is the form and what I put in the fields:

    {6757BEC9-2FF8-4FE6-9077-E8CDD53A62CA}.png

    After Submitting, it adds the row to the form sheet as it should:

    {7CF803BA-F676-42E8-AB52-CB390CA6D1D8}.png

    But on the Final Sheet, this is the output:

    {1B30A92C-F72D-4453-B121-187CFAE31E84}.png

    I expected it to only output the LAST row into 5 rows, one for each item in the multi-select field. Just like you had in your original post.

    Below is how i expected it to look:

    {91A80B15-6B28-4E52-B527-2D4B108DEBD2}.png

    Where's the disconnect? Can someone please tell me:

    1. Which rows do what?
    2. Which fields are output?
    3. Which fields and values matter in the form?

    My specific use is for ordering supplies. so the products would be things like "Pen, pencil, paper, staples" etc. instead of the Options A-J.

    · Share on FacebookShare on Twitter
  • NeilKY
    NeilKY ✭✭
    edited 11/14/24

    PS. Thanks in advance!

    · Share on FacebookShare on Twitter

Categories