Search code examples
sortinggoogle-sheetsformulaflatten

How to sort the answer based on the timestamp after flatten the matrix in google sheet?

enter image description here

Hi everyone,

My goal is to flatten the Answers in C4:E7 into one column and then sort it based on the ascending order of the Submission Timestamp, then sort it again from Answer1 to Answer3.

For example in the screenshot above, Student B submit the answers at 2:49:27pm, which is the earliest among 4 students, so his answer should be on the top of the column and start from Answer 1 to Answer 3 then only follow by the answer from Student A and Student D.

I'm using =QUERY(FLATTEN(C4:E7),"Select * where Col1 is not null") now. I'm not sure how to sort it based on timestamp first in this case.

Column I is the expected output.

Hope to get some help on this issue, any help will be greatly appreciated!

Solution

  • Try:

    =arrayformula(query(iferror(split(flatten(if(A4:A<>"",B4:B&char(9999)&C4:E,)),char(9999)),),"select Col2 where Col2 !='' order by Col1,Col2",0))

    NOTES:

    The starting point is:

    =arrayformula(if(A4:A<>"",B4:B&char(9999)&C4:E,))

    This repeats your 'Submission time' column with each of the 3 answer columns, separated by a character that is unlikely to be used in your data set char(9999) (✏).

    enter image description here

    Then flatten() puts them in 1 column:

    enter image description here

    split() is then used on ✏ to get the results into 2 cols, but you'll need iferror() to stop a formula issue working down the sheet.

    enter image description here

    Then the query() wraps around the result to select col2 (where it's not empty), and sort by Col1,Col2.

    Alternative with filter() so you don't need the iferror():

    =arrayformula(query(split(flatten(filter(B4:B&char(9999)&C4:E,B4:B<>"")),char(9999)),"select Col2 where Col2 !='' order by Col1,Col2",0))