Search code examples
google-sheetsgoogle-query-language

Find which users are working on the same JobID

I am pulling only the JobID column from each User Sheet in a Google spreadsheet. I want to query which Users are working on the same JobID. I need to also list the User's name as a Column of the query as that column is not present in each User Sheet, though the Sheet name is the User's name. Is there someway to do this using Google Query? e.g.

JobID |  Count | Users using it                    |
----------------------------------------------------
1001  |   3    | alex, hubert, mark                |
1002  |   1    | mark                              |
1003  |   2    | sally, chaz, maha                 |
1004  |   1    | hubert                            |
1005  |   2    | maha, mark                        |

P.S: Also, Sheets could be renamed to some other user. so needed something dynamic.

Here is the link to the Sample file.

Solution

  • Based on your comments, I've got a single formula to return the JobIDs and the users. But to include the count of the number of users, I had to add another column and formula. See below:

    enter image description here

    The key formula, in D1, is as follows:

    ={"Job ID","Users (hidden)";
  ArrayFormula(trim(split(regexreplace(REGEXREPLACE(transpose(query(query({
     query({ Users!A$1 & "♦️" & row(indirect(Users!A$1 & "!A$2:A")), indirect(Users!A$1 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$2 & "♦️" & row(indirect(Users!A$2 & "!A$2:A")), indirect(Users!A$2 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$3 & "♦️" & row(indirect(Users!A$3 & "!A$2:A")), indirect(Users!A$3 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$4 & "♦️" & row(indirect(Users!A$4 & "!A$2:A")), indirect(Users!A$4 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$5 & "♦️" & row(indirect(Users!A$5 & "!A$2:A")), indirect(Users!A$5 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$6 & "♦️" & row(indirect(Users!A$6 & "!A$2:A")), indirect(Users!A$6 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0) },
     "select max(Col1) where Col2 <>'' group by Col1 pivot Col2",0)
     ,,99^99)),
     "♦️[0-9 ]+" , ", "),
     "(.*),", "$1"),
     "♥️",0)))}

    This returns an array of JobID and users, comma delimited. It also includes the header labels To add in the count of users, in the format you show, I hid the users column, added a column where I counted the number of users, and then added a following column that replicated the hidden users column. There are lots of other ways of doing this, if you prefer.

    The next key issue is that the formula fails if it includes a user (a row in Users) who does not have a tab, WITH a JobID in it as data. There are several ways around this. One is to extra tabs (twenty or whatever your max users will be), given them names like TEMP1, TEMP2, etc. and have one JobID as filler in each of them, with a value of 999999, or similar. Then the main formula query could exclude records with that JobID. This would mean each user query would always return at least one data row, for JobID 999999, and not fail. When you get a new user, you would just replace TEMP1 in the User tab with their real name, and rename the TEMP1 tab to their name.

    There may be other error checking that could prevent this, but I found that stacking several queries gave me an error if one query returned no rows. Eg.:

    { query(indirect(Users!A$1 & "!A$2:A").........);
  query(indirect(Users!A$2 & "!A$2:A").........);
  query(indirect(Users!A$3 & "!A$2:A").........);
  query(indirect(Users!A$4 & "!A$2:A").........)   }

    gave me an error if either User!A2 was blank, or there was not a tab matching the name in cell User!A2, or the tab with that name had no data.

    If you take those measures, then the main formula can be preset to handle twenty (or more) users. The formula I've shown above is for the current six users, but I've tested it with more. I'll provide the formula for twenty users at the bottom.

    Formula for twenty users. Each "user" have a name, or a dummy name placeholder, in Users, must have a tab with that name, and there must be at least one JobID value in each tab, or a dummy JobID, perhaps like 999999.

    ={"Job ID","Users (hidden)";
  ArrayFormula(trim(split(regexreplace(REGEXREPLACE(transpose(query(query({
     query({ Users!A$1 & "♦️" & row(indirect(Users!A$1 & "!A$2:A")), indirect(Users!A$1 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$2 & "♦️" & row(indirect(Users!A$2 & "!A$2:A")), indirect(Users!A$2 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$3 & "♦️" & row(indirect(Users!A$3 & "!A$2:A")), indirect(Users!A$3 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$4 & "♦️" & row(indirect(Users!A$4 & "!A$2:A")), indirect(Users!A$4 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$5 & "♦️" & row(indirect(Users!A$5 & "!A$2:A")), indirect(Users!A$5 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$6 & "♦️" & row(indirect(Users!A$6 & "!A$2:A")), indirect(Users!A$6 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$7 & "♦️" & row(indirect(Users!A$7 & "!A$2:A")), indirect(Users!A$7 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$8 & "♦️" & row(indirect(Users!A$8 & "!A$2:A")), indirect(Users!A$8 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$9 & "♦️" & row(indirect(Users!A$9 & "!A$2:A")), indirect(Users!A$9 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$10 & "♦️" & row(indirect(Users!A$10 & "!A$2:A")), indirect(Users!A$10 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$11 & "♦️" & row(indirect(Users!A$11 & "!A$2:A")), indirect(Users!A$11 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$12 & "♦️" & row(indirect(Users!A$12 & "!A$2:A")), indirect(Users!A$12 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$13 & "♦️" & row(indirect(Users!A$13 & "!A$2:A")), indirect(Users!A$13 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$14 & "♦️" & row(indirect(Users!A$14 & "!A$2:A")), indirect(Users!A$14 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$15 & "♦️" & row(indirect(Users!A$15 & "!A$2:A")), indirect(Users!A$15 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$16 & "♦️" & row(indirect(Users!A$16 & "!A$2:A")), indirect(Users!A$16 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$17 & "♦️" & row(indirect(Users!A$17 & "!A$2:A")), indirect(Users!A$17 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$18 & "♦️" & row(indirect(Users!A$18 & "!A$2:A")), indirect(Users!A$18 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$19 & "♦️" & row(indirect(Users!A$19 & "!A$2:A")), indirect(Users!A$19 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0);
     query({ Users!A$20 & "♦️" & row(indirect(Users!A$20 & "!A$2:A")), indirect(Users!A$20 & "!A$2:A")&"♥️"}, "where Col2<> '♥️'",0) },
     "select max(Col1) where Col2 <>'' group by Col1 pivot Col2",0)
     ,,99^99)),
     "♦️[0-9 ]+" , ", "),
     "(.*),", "$1"),
     "♥️",0)))}