I have a Google Form linked to a spreadsheet. The responses are filtered to separate sheets based on category. In the filter, the submission times from the Google Form entries are converted to date (clock time is not included). The filter is wrapped around UNIQUE() so if someone submits 10 of the same link in 1 day, only 1 link will appear for that day, but it can appear again on another day if submitted again (once per day).

The separate sheets are the "review" sheets, and the data from those sheets are compiled to another sheet with array {} to have all 5 sheets combined, and then there is a different spreadsheet using IMPORTRANGE() to display the data from that last sheet.

Each array is sorted with newest date at top, and last row at top. But I do not find a way to sort the entire range to show newest (last rows) at the top no matter what sheet it is from. I could do it by filtering the orginal timestamp with clock time, but then I would get multiple duplicates per day. I can't completely ignore duplicates either. Any tips for me? >.<

Example:

Links1 =UNIQUE({ARRAYFORMULA(TO_DATE(DATEVALUE(A2:A4))), B2:B4})

Links2 =UNIQUE({ARRAYFORMULA(TO_DATE(DATEVALUE(C2:C5))), D2:D5})

Links3 =UNIQUE({ARRAYFORMULA(TO_DATE(DATEVALUE(E2:E7))), F2:F7})

=SORT({SORT({A11:C}, ROW(A11:C), FALSE); SORT({E11:G}, ROW(E11:G), FALSE); SORT({I11:K}, ROW(I11:K), FALSE)}, 2, FALSE)

Here is the same as above, just in a spreadsheet if that is easier

CodePudding user response：

The core issue seems to UNIQUE, where you want

• all the unique links
• exactly once per day

To do this, you're mutating the data to remove time data from timestamps, irrecoverably damaging the original data. To preserve timedata and UNIQUE, use SORTN instead:

Minimal Reproducible Example:

Input:

Output:

Formula:

=SORTN(A2:B8,2^99,2,2,1,DATEVALUE(A2:A8),1)

This sorts by DATEVALUE(a virtual column), but it's thrown away and the original data is preserved. It also removes duplicates in the data, based on the virtual DATEVALUE sort column.