TRANSFORM ROW WISE DATA INTO ONE COLUMN CONSECUTIVELY based on the number of values in each row

Question

I've learned how to use filters to organize row-wise data into columns and am taking that to heart in this spreadsheet! But I've hit a major snag.

If you look at the example I've attached here, you'll see that I've successfully built out columns S through X - and everything is working with either filtering or SQL queries to data located elsewhere in this workbook - some from this sheet, some from other sheets . and it all aligns perfectly!

But here's the problem. As you can see in columns Z through AG, the answers to each survey columns are arrayed row-wise. When I try to filter columnns AA through AG and place the results in column V, I run into two problems: 1) it stacks everything from AA, then AB, then AC etc. That's exactly wrong! I need it to stack Row 3's values from AA through AD as I have done in V3 through V6. But the problem is that I haven't figured out how to do this using an array formula - and the numbers of answer options for each question differ - anywhere from 2 to 7. I've got a helper column in Z which shows how many data points there are per question - and I was hoping to get that to operate as a control to determine how many datapoints I would need to write into column V. Couldn't figure that one out. So - I wrote a ridiculous formula to process the control in column Z in order to determine how many cells (from 1 to 7, AA to AG) to write out, transposed. But then, I can't do that in a single column using ARRAYFORMULA - which defeats the entire effort.

Everywhere you see a light green cell, there's a formula in there.

So - here are the formulas:

Column S (easy enough): =query($O3:$Q,"select O where O > 0 order by O asc", 0)

Column T (also easy enough): =arrayformula(if(isblank($S3:S),"",if($S3:$S<>$S2:$S,vlookup($S3:$S,ExitPollSETUP!$C28:$E,{2,3},false),""))) ---this is a simple breakpoint tool to only label the first row in each group so the labels don't repeat (and it also pulls in column U).

I'll jump over Column V for a second - that's the "problem area".

Column W and X are simple queries retrieving already-filtered data which matches up correctly with Column S. (I've validated all of this and it's working perfectly). Here's that formula: =query($O3:$Q,"select P,Q where O > 0 order by O asc", 0)

Now - for Column V - you can see that I want to fit in exactly the columns AA through AG, transposed into a single column, but only based on the number of answers, which is the control in Column Z. I want to do this recursively so that it all falls in as shown in the document. But I couldn't figure out how to do that in an array formula - which is the big problem I'm having - so instead I've had to paste in the same dumb formula for each new question and that's exaclty what I don't want to do!

Here's the formula currently being used for each answer in Column V: =if($T3<>"",IF($Z3=7,transpose($AA3:$AG3),if($Z3=6,transpose($AA3:$AF6),if($Z3=5,transpose($AA3:$AE3),if($Z3=4,transpose($AA3:$AD3),if($Z3=3,transpose($AA3:$AC3),if($Z3=2,transpose($AA3:$AB3),"")))))),"")

See what I'm doing here? Selecting the number of cells to transform using the helper column.

So - bottom line - how can I go a row at a time through the entire set of AA to AG and then transpose all of it - left to right per row, and continue moving through the data left to right per row, and place it correctly in the first row of each answer, in Column V ????

I'm bewildered by this - and would LOVE an answer. Thanks for reading this.

Gary K.

(BTW-I'm running this with an API query to a highly secure data set and can't share the sheet because the data is being generated with a KEY. That's why I've cut out a sample of the sheet and pasted it here and provided the relevant formulas above)

Answer 1

This formula takes each question and its standard answers in a single row, and re-formats the data to the desired output.

=query({query({A:I},"select Col1,Col2,Col3,Col4");query({A:I},"select Col1,' ',' ', Col5 where Col5 is not null LABEL ' ' '', ' ' ''",0);query({A:I},"select Col1,' ',' ',Col6 where Col6 is not null LABEL ' ' '', ' ' ''",0);query({A:I},"select Col1,' ',' ',Col7 where Col7 is not null LABEL ' ' '', ' ' ''",0);query({A:I},"select Col1,' ',' ',Col8 where Col8 is not null LABEL ' ' '', ' ' ''",0)},"where Col1 is not NULL order by Col1")

---

• The query is nested, each additional response separated by an array separator (;)

• For the second and subsequent responses, there is a "where" to test for the existence of the response.

• The formula (as written) assumes that the maximum number of responses = 6. But if the maximum is (or becomes) greater than 6, then it is a simple matter to add an extra query into the nested array.

---

SAMPLE

---