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 :))

enter image description here enter image description here

1 Answer 1


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

  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().

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.