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)