How to filter a given shift schedule table to extract the dates and the shift codes where 2 workers of your choice have the same shift?

Question

Suppose we have a given shift schedule table like in the picture I would like to create a new worksheet in which i will choose 2 different names, and the result will be three rows: Date,Worker 1,Worker 2

I want it to show me the dates in Row "Dates", and the common shifts in the rows "Worker 1" and "Worker 2". How can i do it ?

Link of the spreadsheet: https://docs.google.com/spreadsheets/d/1lm9BO-xrlF2Ie1Sz7yjERW5ixXQr-Oi2Sp5BEbNaRWI/edit#gid=1314026963

Thank you :))

Answer 1

Use bycol(), filter() and match(), like this:

=let( 
  names; Shifts2!A1:A; 
  table; Shifts2!B1:ZZ; 
  worker1; A2; 
  worker2; B2; 
  matches; bycol(table; lambda(col; let( 
    date; chooserows(col; 1); 
    shifts1; filter(col; names = worker1); 
    shifts2; filter(col; names = worker2); 
    vstack(date; filter(shifts1; match(shifts1; shifts2; 0))) 
  ))); 
  valid; transpose(filter(matches; len(chooserows(matches; 2)))); 
  query(valid; "where Col2 <> 'DO' "; 0) 
)

To show all matches, including shifts that show DO, leave out the query().

See bycol(), filter() and match().