1

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

2

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.

  • Thank you. I am trying to clean it up while I figure out how I need it to look. JPV - how do I sort the ArrayFormula based on Timestamp now that I've included Timestamp in the Sorted spreadsheet. Thanks to you both! – Bardo Nov 11 '15 at 16:51
  • 1
    The two last numbers (1, 1) of the sort function refer to the column to sort by (1) and a boolean (true or 1; false or 0) that indicates if the sorting needs to be ascending or not. So you would have to change the column to 6. – JPV Nov 12 '15 at 9:03
1

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))
  1. 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.