Unique function for four columns Google Sheets

Question

I'm stuck at this small project. Basically I'm trying to create a time monitoring program. I have four columns, Column A(Timestamp) / Column B(Staff Name) / Column C(Date) which is extracted from Column A, and Column D(time) extracted also from Column A.

I already have the unique list for this, but the only records I can extract are the Column B, and C, since those are my criteria when looking for unique records. This is the code I use:

=UNIQUE('ORIGINAL LIST'!$B2:C)

Here's my result:

Now I'm stuck on how do I include the Column A(timestamp) for those Unique records. I've tried using if statements, vlookups, arrays, but I wasn't able to get the result I want. Last thing I tried is this logic: if column B and column C from "UNIQUE LIST" sheet matches column B and C from "ORIGINAL LIST" then it will copy the column A. However I get the error "Array arguments to EQ are of different size", which make sense since the ORIGINAL LIST have duplicates.

Here's a LINK for my sheet.

Hope someone can help.

player0 player0 · Accepted Answer · 2021-02-08T06:28:05

try:

=INDEX(QUERY(TEXT(ARRAY_CONSTRAIN(SORT(SORTN(
 {'ORIGINAL LIST'!A2:D, 'ORIGINAL LIST'!B2:B&'ORIGINAL LIST'!C2:C}, 
 9^9, 2, 5, 1)), 9^9, 4), 
 {"m/d/yyyy hh:mm:ss", "@", "mm/dd/yyyy", "hh:mm:ss AM/PM"}), 
 "where Col4 is not null"))

 {A2:D, B2:B&C2:C} - join columns B and C as 5th column in array {}
 9^9 - all rows
 2 - grouping mode of sortn
 5 - group by 5th column
 1 - in ascending order
 9^9 - return all rows
 4 - return only first 4 columns
 {"", "", "", ""} - format each column 
 "@" - output format equals the input format
 "where Col4 is not null" - where 4th column is not empty

Unique function for four columns Google Sheets

1 Answers