Index - Offset - Array Formula

1
votes

I am using OFFSET function to transpose the "Question" columns to rows so every row would correspond to a Link/Question combination as shown in the 2nd table.

I can hide values in columns A & B if column C is blank but this is taking TOO MUCH space in my table (my full data consist of 11 questions, and hundreds of rows, which won't fit in my sheet if I create a row for every Question column). I need to transform my functions to an array formula that would only populate a row for every Link/non blank Question combo, so that row 9 in the image below won't show up and its formula won't exist at all. I'm using Gsheets.enter image description here

1st function for column A 2nd function for column C

OFFSET($A$2,FLOOR((ROW($A1)-ROW($A$1))/2,1),0)
OFFSET($C$2,FLOOR((ROW(A1)-ROW($A$1))/2,1),MOD((ROW(A1)-ROW($A$1))/2,1)*2)
2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

2 Answers

3
votes

Please try in A7:

=query({query(A2:C3,"select A,B,C where C is not NULL");query(A2:D3,"select A,B,D where D is not NULL")})

As mentioned by @Annan (thank you!) the outer query is redundant.

0
votes

This really IS the same as in excel.. but I can't help the " only populate a row for every Link/non blank Question combo" part. Try :

A7    =if($C7="","",index($A$2:$D$3,int((row(A7)-7)/(COLUMNS($A$2:$D$3)-2))+1,match(A$6,$A$1:$D$1,0)))

B7    =if($C7="","",index($A$2:$D$3,int((row(B7)-7)/(COLUMNS($A$2:$D$3)-2))+1,match(B$6,$A$1:$D$1,0)))

C7    =index($A$2:$D$3,int((row(C7)-7)/(COLUMNS($A$2:$D$3)-2))+1,(2+if(mod(row(C6)-5,COLUMNS($A$2:$D$3)-2)=0,COLUMNS($A$2:$D$3)-2,mod(row(C6)-5,COLUMNS($A$2:$D$3)-2))))

and drag to A10:C10.

Hope it help it'll be useful though.. :)