I have a table where each entry includes assignee, task name, start date, and due date.
Here is sample data:
| Owner | Task | Start Date | Due Date | Duration (days) |
|---|---|---|---|---|
| Jake | Task 1 | 2/21/2022 | 2/24/2022 | 4 |
| Jake | Task 2 | 2/28/2022 | 3/10/2022 | 9 |
| Jake | Task 3 | 3/14/2022 | 3/17/2022 | 4 |
| Fred | Task 4 | 3/21/2022 | 4/28/2022 | 29 |
| Fred | Task 5 | 5/2/2022 | 5/12/2022 | 9 |
| Jake | Task 6 | 5/16/2022 | 6/16/2022 | 24 |
| Jake | Task 7 | 6/20/2022 | 6/30/2022 | 9 |
| Jake | Task 8 | 7/4/2022 | 7/14/2022 | 9 |
| Jake | Task 9 | 7/18/2022 | 8/11/2022 | 19 |
| Jake | Task 10 | 8/15/2022 | 12/8/2022 | 84 |
| Jake | Task 11 | 12/12/2022 | 1/5/2023 | 19 |
| Erica | Task 1 | 1/9/2023 | 2/2/2023 | 19 |
| Erica | Task 2 | 2/6/2023 | 3/2/2023 | 19 |
| Erica | Task 3 | 3/6/2023 | 6/15/2023 | 74 |
| Erica | Task 4 | 2/21/2022 | 2/24/2022 | 4 |
| Erica | Task 5 | 2/28/2022 | 3/10/2022 | 9 |
| Erica | Task 6 | 3/14/2022 | 3/17/2022 | 4 |
| Erica | Task 7 | 3/21/2022 | 6/16/2022 | 64 |
| Erica | Task 8 | 6/20/2022 | 6/23/2022 | 4 |
| Erica | Task 9 | 6/27/2022 | 6/30/2022 | 4 |
I need to take that information, and display it on a roadmap, but with all tasks for one individual on a single row. Currently, I have tried the following:
=TRANSPOSE(QUERY(Sheet1!A1:E21, "Select B Where A='"&D5&"'", 1))
I would like the task name to appear when the start date matches, and the ability to conditionally format all cells that are part of the duration. I have tried several approaches but don't seem to be making progress beyond my current point.
| Assignee | Num Tasks | Mar-7 | Mar-14 | Mar-21 | Mar-28 | Apr-4 | Apr-11 | Apr-18 | Apr-25 | May-2 | May-9 | May-16 | May-23 | May-30 | Jun-6 | Jun-13 | Jun-20 | Jun-27 | Jul-4 | Jul-11 | Jul-18 | Jul-25 |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| Jake | 14 | Task1 | Task2 | Task3, | Task4 | Task5, | Task6 | Task7, | Task8 | Task9, | Task10 | Task11 | ||||||||||
| Erica | 6 | Task1 | Task2 | Task3 | Task4 | Task5 | Task6 | Task7 | Task8 | Task9 |
Here is an image of the desired output as well.
CodePudding user response：
Given this question type's popularity that Matt refers to in his answer, perhaps it is worthwhile to mention the "conventional" filter() pattern often used to create these sorts of timelines.
With task owner name, task name, start date and due date in columns A2:D, you can list task owners in cell G2 with =unique(A2:A) and the number of tasks in cell H2 with =counta( iferror( filter( B$2:B, A$2:A = G2 ) ) ).
To list all the weeks from the first start date to the last due date, put this in cell I1:
=sequence(1, (max(C2:D) - min(C2:D)) / 7, min(C2:D), 7)
Format the sequence() results as Format > Number > Date.
Finally, to list task names by owner name and week, put this in cell I2:
iferror( textjoin( ", ", true, filter( $B$2:$B, $A$2:$A = $G2, $C$2:$C <= I$1, I$1 <= $D$2:$D) ) )
See the '