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?