0

I am using Google Forms to collect information. In google sheet, I have rows of data and one of the cells contains lists that needs to be separated out.

=query(FLATTEN(ArrayFormula(IFERROR(SPLIT(G2:G,char(10))))),"where Col1 is not null",0)

This Formula Works to Split Multiple entries from a single cell(G2:G) into a list

I would like to have the "Title"(B2:B) of the responses collected to be combined with the entries that were split form the "List"(G2:G)

=query(FLATTEN(ArrayFormula(IFERROR(SPLIT(G2:G,char(10))&B2:B))),"where Col1 is not null",0)

This does split the List in the cells, but adds the title directly to the last character of the list item. Also, it adds more rows of just the "Title"(b2:b)

=query(FLATTEN(ArrayFormula(IFERROR(SPLIT(G2:G,char(10))&" - "&B2:B))),"where Col1 is not null",0)

This Gives me error message: Result was not automatically expanded, please insert more rows (454494).

I also tried to use the working SPLIT Formula(Q2:Q) from above, and used index and match with it/

=ArrayFormula(iferror(index($B$2:$B, Match("*"&Q2:Q&"*",$G$2:$G,0),1)&" - "&Q2:Q ,""))

This returned the "Title" in B2 combined with all the split entries. If Failed to recognize the other "Title"

=arrayformula(if(Q2:Q="","",Match("*"&Q2:Q&"*",$G$2:$G,0)))

I split out the match formula and it does return the proper row # of the "Title" where the SPLIT List Entries came from.

Here is a sample Google Sheet https://docs.google.com/spreadsheets/d/10gSSB4RE8049OlTh-FagxrZ2Bg0Mt7o0n4OE-gr-0IM/edit?usp=sharing

The outcome I am Looking for is to have the "Title"B2:B combined in the entries of that were SPLIT from "List" G2:G

Improve this question
3
  • 1
    In your sheet, you've shown a lot of what you don't want or told about what doesn't work. But what would be most helpful (which remains absent currently) is a manually entered list showing somewhere what you do want. I recommend adding that to your spreadsheet, because I myself am still not clear on the desired output, so I assume other contributors will benefit from the clarification as well.
    – Erik Tyler
    Nov 12, 2021 at 18:05
  • B2 = Carl G2 ="Pizza Hot dogs Pretzels" Desired output in Column H2:H is combined text of B2 with every Split entry from G2. So H2 = Carl - Pizza H3 = Carl - Hot Dogs H4 = Carl - Pretzels
    – T D
    Nov 13, 2021 at 1:57
  • Did you try the formula I posted below, seven hours before your above comment?
    – Erik Tyler
    Nov 13, 2021 at 2:55

1 Answer 1

Reset to default
0

Please see my comment added directly to your post.

In the meantime, I will take a guess that the following might be what you want:

=ArrayFormula(FLATTEN(FILTER(SPLIT(SUBSTITUTE(CHAR(10)&G2:G,CHAR(10),"~"&B2:B&" - "),"~"),B2:B<>"")))

(which should be placed in the Row-2 cell of a column that is otherwise empty from there down).

Addendum (based on comments):

Your posted data is in parallel form. That is, each person has only and exactly three food items associated. If that kind of exact parallel will not exist in your real data, it's best to rule out the blanks from the virtual grid that is formed before FLATTEN in the following way:

=ArrayFormula(QUERY(FLATTEN(FILTER(SPLIT(SUBSTITUTE(CHAR(10)&G2:G,CHAR(10),"~"&B2:B&" - "),"~"),B2:B<>"")),"Select Col1 WHERE Col1 Is Not Null"))

Improve this answer
5
  • This works great. It did add 3 Blank rows, but I just threw SORT in there and its perfect. =ArrayFormula(sort(FLATTEN(FILTER(SPLIT(SUBSTITUTE(CHAR(10)&G2:G,CHAR(10),"~"&B2:B&" - "),"~"),B2:B<>""))))
    – T D
    Nov 13, 2021 at 12:09
  • All of your example data was parallel. That is, each person had only and exactly three food items associated. This formed a regular grid (i.e., the same width as height). If that will not always be the case, there is a better way to rule out blank rows. See the addendum to my post.
    – Erik Tyler
    Nov 13, 2021 at 15:24
  • Didn't see the addendum before, but I looks Like it works well. Thanks for taking the time to help me out!
    – T D
    Dec 18, 2021 at 16:53
  • You're welcome. I hope reading my comment and the addendum over a month after I wrote it was still helpful.
    – Erik Tyler
    Dec 18, 2021 at 17:52
  • Of course it was helpful, and I have put it in play since you gave me the original solve in the first place. i appreciate the help and the refinement, it helps understand how the formulas work...or at least look I understand
    – T D
    Dec 23, 2021 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.