A formula to get the first or last instance of a specific criteria against a list of names

user_18543435 0
Viewed 16

I have a list of multiple instances of the same worker with multiple instances of work and rest. I need the name of the worker and time of the first instance of Work and the time of the last instance of Rest.

Per person I can get the first work time if I type the actual range by this formula =index(Filter(C2:D9,C2:C9="work"),1,2) This is Col G I can determine how many entries of Rest there are for the same person to then work out the time of the last rest with =index(Filter(C2:D9,C2:C9="rest"),E2,2). This is Col H And I can get a unique list of names with this formula =sort(unique(A2:A)) this is Col F

But I can't work out the formula to look at Col F against Cols A to D then return First Work Time in Col G And a formula to look at Col F (against Cols A to D) and return the Last Rest Time in Col H

My data is in columns A to D and the results I would like are shown in Cols F to H

    A           B            c  d   e   f           g   h
1   Name        Date          Action    Time            Name        First Work  Last Rest
2   Smith, Fred 14-03-2022  rest    00:00   4   Smith, Fred 06:03   07:08
3   Smith, Fred 14-03-2022  work    06:03   2   Jones, Harry    07:48   08:08
4   Smith, Fred 14-03-2022  rest    06:05               
5   Smith, Fred 14-03-2022  work    06:06               
6   Smith, Fred 14-03-2022  drive   06:15               
7   Smith, Fred 14-03-2022  rest    06:59               
8   Smith, Fred 14-03-2022  drive   07:02               
9   Smith, Fred 14-03-2022  rest    07:08               
10  Jones, Harry    14-03-2022  rest    00:00               
11  Jones, Harry    14-03-2022  work    07:48               
12  Jones, Harry    14-03-2022  drive   08:01               
13  Jones, Harry    14-03-2022  work    08:03               
14  Jones, Harry    14-03-2022  drive   08:04               
15  Jones, Harry    14-03-2022  work    08:07               
16  Jones, Harry    14-03-2022  rest    08:08
google-sheets-formula
1 Answers

I understand you would want the First work and Last rest to be aggregated by name (as you have indicated) and also by date (so that for each name you would get one row with First work and Last rest for each date, if there are multiple dates associated with a given name).

With a data structure as in your example (A: Name, B: Date, C: Action, D: Time), you can use the following formula (you do not need a list of names):

={QUERY(A:D,"select A, B, min(D) where C='work' group by A, B label min(D) 'First work'",1),
QUERY(A:D,"select max(D) where C='rest' group by A, B label max(D) 'Last rest'",1)}

This assumes that there is at least one work and one rest for each name for each date, otherwise it will get misaligned or show error (if the amount of 'work' and 'rest' results are not equal).

If this is possible with your actual data, then it is safer to display the data as two separate tables for First work:

=QUERY(A:D,"select A, B, min(D) where C='work' group by A, B label min(D) 'First work'",1)

and for Last rest:

=QUERY(A:D,"select A,B, max(D) where C='rest' group by A, B label max(D) 'Last rest'",1)

I hope it is easy to understand what the queries are doing, if not then consult the documentation of the QUERY formula and Query Language or ask if something is still not clear.

user_200146330
Related