Sorting Pre-Sorted Google Form Data by Timestamp

Question

I have Google Form checkbox data that I am separating out into another table so that each checkbox selection generates its own line.

I received some help in pulling the data over, and the resulting table was sorted alphabetically. What I need is for that table to sort by timestamp with the timestamp data brought over as well.

The Form Responses tab is obviously where the responses are collected and the Sorted tab is where I need the data sorted by timestamp with the timestamp included.

Link

Answer 1

In addition, if you don't mind the timestamp to be in the last column, you can change the 'sorting formula' to

=sort(ArrayFormula({transpose(split(regexreplace(join(";", 'Form Responses 1'!DD2:DD), "(\w+),\s(\w+),\s", "$1, $2;"),";")), VLOOKUP(TRANSPOSE(SPLIT(JOIN("/", REPT('Form Responses 1'!DI2:DI&"/", if(len('Form Responses 1'!DD2:DD), (len('Form Responses 1'!DD2:DD)-len(substitute('Form Responses 1'!DD2:DD, ",",""))+1)/2,)) ),"/")), {'Form Responses 1'!DI2:DM, 'Form Responses 1'!A2:A}, {1, 2, 3, 4, 5, 6}, 0) }),1,1)

Note that the REPT() function has a limit of 32000 characters ... Also make sure to format the column that will have the timestamps as DATE/TIME.

Answer 2

You can pull over timestamps into Sorted sheet as follows:

1. Insert Column A, Timestamp, in Sheet Sorted. Format it as Date Time.

2. In cell A2, enter

=filter('Form Responses 1'!A:A, regexmatch('Form Responses 1'!DD:DD, B2)*regexmatch('Form Responses 1'!DL:DL, F2))

3. Drag this formula down the column A; it will populate it with timestamps. (May take a little while to run).

The idea is that regexmatch identifies the matches for student name and teacher's name. Then filter takes the timestamp of the (unique) matching row.

However, you will not be able to simply sort the Sorted sheet by the timestamp, because it's being generated by a complex Arrayformula which already imposes some kind of sort. If you want them sorted differently, you can copy everything from Sorted to another sheet, pasting values only (Ctrl-Shift-V). Then sort in any way.

The Form Responses sheet is quite... complex. I think it would be best to redesign it with an view toward the data processing required; the present form may look good on the user-facing side, but the backend is ugly.

The time-intensive formulas generating Sorted sheet will not perform well (if at all) when you get several hundreds of referrals in the system.