0

In sheet1, I have something like this (A to D are headers - sample sheet here):

A B C D
X X1 X2
Y Y1 Y2 Y3
Z Z1

And in sheet2, I want something like this:

A B
X X1
X2
Y Y1
Y1
Y1
Z Z1

*Values in column A exists only on the first instance (it's not merged with the cells below it)

Sheet1 data comes from google form submissions, but we want to structure them as to the table sample in sheet2, where sheet1 columns B to D are stacked in sheet2 column B.

For now, we're using the following to merge columns B to D in a single cell aligned with the values in column A:

=ArrayFormula(Sheet1!A:A&CHAR(10)&Sheet1!B:B&CHAR(10)&Sheet1!C:C&CHAR(10)&Sheet1!C:C&CHAR(10)Sheet1!D:D)

However, this presents a lot of problems since line breaks would still be there even when there's no supposed second line, and that we have to manually update status of these items (since they're used for monitoring).

If we can have it line by line as what is expected, we'd be able to automate some of the tasks. We tried playing with QUERY, but to no avail (although I think it's possible via that function... not sure).

Hoping to get ideas from the community. Thanks!

Improve this question
2
  • I would suggest that the most efficient and effective way to receive help is to share a link to a sample spreadsheet. Name the tabs the same as they are in your actual sheet. Set up the ranges in the same places. And be sure to set the link's Share permission (when you create the link) to "Anyone with the link..." and "Editor." This is the easiest way for others to begin immediately and test solutions; and it makes carrying formulas over to your other sheet easier, since they shouldn't need adjustment if the setup of the sample sheet is the same. Commented Mar 12, 2021 at 3:03
  • @ErikTyler Thanks for suggesting; already added a sample google sheets link. Commented Mar 12, 2021 at 6:02

1 Answer 1

Reset to default
2

I've added a new sheet ("Erik Help") with the following formula in A1:

=ArrayFormula({"Header 1", "Header 2";QUERY(SPLIT(FLATTEN({FILTER(INDIRECT("Sheet1!A2:A"),INDIRECT("Sheet1!A2:A")<>"")&"|"&FILTER(INDIRECT("Sheet1!B2:B"),INDIRECT("Sheet1!A2:A")<>""),IF(FILTER(ROW(INDIRECT("Sheet1!A2:A")),INDIRECT("Sheet1!A2:A")<>""),"")&"|"&FILTER(INDIRECT("Sheet1!C2:D"),INDIRECT("Sheet1!A2:A")<>"")}),"|",1,0),"Select * Where Col2 Is Not Null")})

This formula creates the two headers first.

You'll notice the heavy use of INDIRECT to reference ranges. This is because you'll have form data coming into that sheet; and if the formula doesn't have a way to "lock" ranges, those ranges will shift down one every time a new row is added onto the form-intake sheet. In most other applications, you can "lock" those ranges by using full-column references (e.g., A:A instead of A2:A). But given the specifics of what you're trying to do here, INDIRECT was the least complex approach. Keep in mind that, because INDIRECT is used, the references will not automatically change if you rename Sheet1 to something else (like "Form Responses 1"). You will need to change each reference manually. Or use FIND/REPLACE, select "Specific Range" set to the formula cell, and check the "Also search within formulas" box.

The idea here is that every value from A2:A is concatenated to every value in the same row of B2:D, with a pipe symbol between (as a SPLIT marker for later). Since you only want to see the Col-A values beside Col-B values, Col-A&"|"&Col-B are first processed alone; then a blank is appended instead of Col-A for everything else.

FILTER is used to only process rows for which there is data in Col-A.

SPLIT splits the combinations made (as described above) at the pipe symbol, forming two columns.

QUERY keeps only those results of the SPLIT that have something in the second column.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Thanks Erik! My actual data was actually a lot more complex, with six columns needing to be stacked into one, while 5 other columns needing treatment similar to column A in here. It's good to have your answer as reference though, until I got it right.
It's always best when posting to represent your actual problem in the post (unless you are either comfortable expanding formula concepts or committed to taking a formula apart and studying it as long as necessary to adapt it). At any rate, I'm glad to have gotten you closer to goal. And thank you for marking this post as "Best Answer" for the sake of other contributors and future site visitors.
Upon twisting my mind for a while, I don't think I can expand it further. I guess I'll open a new question on this one with the actual representation of the data. Should have done that in the first place. Thanks for the heads up!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.