Complex rearranging and repeating of column headers via arrayformula possible?
I have a complex survey with numerous skip logic rules that ends up returning over 3 dozen columns of mostly empty data with only certain questions applicable to each respondent's submission. I tried creating a column at the end of the columns to grab any cell in that row that was not blank and concatenating them all into one cell: =ifna(textjoin("|",true,filter($A$2:$AO$2&"_"&A3:AO3,A3:AO3>0)))
This yielded me one cell per row with everything I needed - including the column headers so I could parse the data (without all the blanks) by looking only at that one column.
However, each time a new response comes in, it shifts all the data down so I am constantly needing to go in and add the formula to new responses. I tried moving the formula to another tab completely: =ifna(textjoin("|",true,filter(Eureka!$A$2:$AO$2&"_"&Eureka!A3:AO3,Eureka!A3:AO3>0)))
This formula also will not correct itself once new data appears on the Eureka tab. So I filled that formula down in one long column...it works perfectly on any response up to that point. Then when a new response comes in (at row 274 as an example), all of the formulas below row 274 automatically add a row to the references. So that if my formula in row 274 has ranges like this: A274:AO274...once a response comes in on row 275, my formula on row 275 has jumped up by one like this: A276:AO276 (to 298 or 343...depending on the number of new responses.
So I want to make my formula act as an arrayformula: =ifna(arrayformula(textjoin("|",true,filter(Eureka!$A$2:$AO$2&"_"&Eureka!A3:AO,Eureka!A3:AO>0)))) but textjoin only works on either rows or columns, so this keeps giving me an error.
I think I need to use MAP/LAMBDA possibly or some kind of REPT, but I just can't seem to crack it.
And in full disclosure, my ultimate goal would be to actually have each question returned on its own row so that the first two columns get repeated for every question vertically. But I think once I get the original question addressed, I can figure out how to do that.
The following formula should produce the result you desire:
=BYROW(BYCOL(FILTER(Eureka!A2:AO,Eureka!A2:A <> ""),LAMBDA(col, ARRAYFORMULA(CONCAT(ARRAYFORMULA(IF(ISBLANK(FILTER(col,{FALSE;TRANSPOSE(SPLIT(REPT(TRUE&CHAR(127),ROWS(col)-1),CHAR(127),TRUE,TRUE))})),,ARRAY_CONSTRAIN(col,1,1)&"_")),FILTER(col,{FALSE;TRANSPOSE(SPLIT(REPT(TRUE&CHAR(127),ROWS(col)-1),CHAR(127),TRUE,TRUE))}))))),LAMBDA(row,TEXTJOIN("|",true,row)))
