Search code examples
google-sheetsgoogle-sheets-formula

Google Sheets: Many to Many Table Join

I am trying to create a sheet to determine the amount of overlapping hours for employees.

I have one table with timeclock data for the Employees.

Table 1

And another with timeclock data for their Support Staff.

Table 2

This is the desired output. Each row from table A has all the date matches from table B. From here I would compute the number of overlapping hours in the final column and then roll that up into another sheet.

Table 3, Desired Output

(apologies for image links, I can't post inline images yet)

Sample sheet here Please let me know if you have any ideas for me!

I know its a combination of QUERY, ARRAYFORMULA, FILTER and more but I just can't find the right combo.

Solution

  • Here's a way of doing this type of join using only built-in functions:

    =arrayformula(lambda(employee,support,
lambda(datecomp,
lambda(rows,
{vlookup(index(rows,,1),{row(employee),employee},sequence(1,columns(employee),2),false),
vlookup(index(rows,,2),{row(support),support},sequence(1,columns(support),2),false)})(
split(filter(datecomp,datecomp<>""),"|")))(
flatten(if(index(employee,,1)=transpose(index(support,,1)),row(employee)&"|"&transpose(row(support)),))))(
Employee!A1:D6,Support!A1:E5))

    There's a lot going on here, but the basic idea is that we are comparing the date columns of each table against each other in a 2D IF array, and where the dates match we are obtaining the row index of each table. After some manipulations we can use these row indexes on each table in two side-by-side VLOOKUPs to obtain the joined table.