1

I have a table to keep track of hours put in working on an Interior Design project. The table looks something like this:

8/16/2015   6:30:00 PM     8:00:00 PM    1.50   Floorplan
8/19/2015   10:00:00 AM    10:30:00 AM   0.50   Layout
8/19/2015   12:00:00 PM    3:30:00 PM    3.50   staircase

In another table, I want to consolidate all the dates into a single row that sums up the total amount of hours worked that day and concatenates the descriptions of what was worked on. So I'd like something like this:

    8/16/2015   1.50    Floorplan
    8/19/2015   4.00    Layout, staircase

The first part of making this happen, is writing a Query that could group the dates. This wasn't too difficult and I made it happen with

=QUERY(A2:E4, "select A, sum(D) group by A")

However, since I want to add the concatenated descriptions in the next cell, I've been trying to write a separate query for those cells. However I'm having a major issue, because I'm trying to look at the date on each row but I can't reference the cell from inside the query. So for example, I'm trying to insert this function:

=JOIN(", ", TRANSPOSE(UNIQUE((QUERY(A2:F4, "select E where day(B) = day(A6)))))

This function should get all the unique descriptions corresponding to a particular day (for simplicity ignoring the year and month), and join them up into a single cell. However it is failing because day(A6) is returning nothing even though there is a date in A6 from the previous query.

So the question is, how do I properly use a reference to another cell in a Google Sheets query?

1

Indeed, one cannot put things like "A6" inside the query string because then it would just a part of a string, not a cell reference. Instead, concatenate the value of the cell to the string:

=JOIN(", ", UNIQUE(QUERY(A2:F4, "select E where day(A) ="&day(A6))))

You don't need transpose, because join works equally well with one-dimensional arrays of either direction: vertical or horizontal.


That said, I find a solution using filter slightly more readable:

=join(", ", unique(filter(E$2:E$4, A$2:A$4 = A6)))
  • Thanks! Can you explain what the & in &day(A6) is for, and what the $ in A$2:A$4 is for? – CodyBugstein Aug 24 '15 at 15:21
  • & is concatenation of strings, e.g., "Java" & "Script" -> "JavaScript". The dollar signs are used for absolute cell references - this is a common feature of all spreadsheet software; see, e.g., gcflearnfree.org/googlespreadsheets/14.3 – user79865 Aug 24 '15 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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