Pulling earliest date in specific criteria in google sheets

user_13741352 0
Viewed 1556

Using google sheets, I'm trying to pull the earliest dates for unique values using the query function.

The Raw data looks like this

I want to pull the data so that I only get the first test completed for each unique identifier provided that test was done within 2 days prior or after their ward admission. So it should spit out something like this:

The data range I need

I am using the following formula which is nearly what I want, it's just including multiple values for the unique identifers:

=Query(Sheet1!1:952,"Select A,B,C,D,E where C > -2 and C < 2 and C is not null and E is not null Order By D",1)

Results I'm getting with the above formula

I feel like I'm nearly there, I just need to somehow only pull the minimum date values instead of them all. Any help would be really appreciated!

google-sheets
1 Answers

It's actually easier to use Sortn with these than query (although you can choose the min value of any individual column (e.g. date) within a group using query, it doesn't give you the min value of any other columns corresponding to that min value, which is what you want). Sortn has an option to ignore rows that are duplicates with respect to a particular sort key or keys (in this case, Unique Identifier).

=sortn(sort(filter(A2:E,C2:C<2),4,1,1,1),999,2,4,1)

(if column C can be negative, put another condition in the filter).

enter image description here

user_38949170
Related