Search code examples
arraysgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

Find and combine results from two columns based on same search criteria

I am setting up a staff database and some of the staff have split roles across two teams in the same department. I am using the QUERY function on the Dashboard tab to bring up details for various teams, searching via manager or team. The problem I have is when trying to factor in the split roles into the searches.

For example, an employee who works part-time in one team and part-time in another will be listed under two managers and two teams in the main database. See image below:

Table

What I want it to come up with is as follows when you search for either Manager or Team:

Staff Member - Hours - Role

Person 1 - - - - 37 - - A

Person 2 - - - - 37 - - A

Person 4 - - - - 10 - - B

So all the people who work for a certain manager, whether the role is 1 or role 2 would be shown.

I have tried the following to combine the two column results:

={query(StaffData2,"select B,AT where K = '"&Dashboard!O8 &"' and AT is not null",1);query(StaffData2,"select B,AU where AO = '"&Dashboard!O8&"' and AU is not null",0)}

It seems to half work when the employee in a team does have a split role but it adds them as another entry on to the bottom of the results eg:

Person 1

Person 2

Person 1

Alternatively, if there are no split roles then the result comes back with an ARRAY LATERAL error.

Any help would be great.

Solution

  • the proper way would be:

    =QUERY({B3:E13; B3:B13, F3:H13}, 
 "select Col1,Col2,Col4 where Col3 = '"&C16&"'", 1)

    0

    0