Google sheets: Take tabulated data with start and end dates and transpose to a roadmap view, with it-CodePudding

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, as I think the image captures the desired result just a bit better.

CodePudding user response：

this is an extremely common question, and there are many answers out there on the internet, but there is not a common enough syntax in describing what you want to make it easy to google.

I built something in two steps rather than one, because I've found that with this solution, people often hadn't thought of having their task data "flattened" the way I've done on the helper tab. The database structure of the task dates for the gantt chart will likely be useful for you for lots of other things. I'd recommend that you leave it in place, (but possibly hidden) on your real sheet so that you can reference it for other dashboard-ey purposes.

I made a new tab on this sheet and put this formula in cell D3.

=ARRAYFORMULA(IFERROR(VLOOKUP(B3:B5&D2:2,{MK_Help_Back_End!A:A&MK_Help_Back_End!C:C,MK_Help_Back_End!B:B},2,0)))

It relies on a "back end" helper tab with this formula in cell A1:

=ARRAYFORMULA(QUERY(SPLIT(FLATTEN('Data from OP'!A2:A&"|"&'Data from OP'!B2:B&"|"&'Data from OP'!C2:C SEQUENCE(1,MAX('Data from OP'!E2:E),0)&"|"&'Data from OP'!D2:D),"|",0,0),"select Col1,Col2,Col3 where Col3<=Col4 label Col1'Name',Col2'Task',Col3'Date'"))

CodePudding user response：

try:

=INDEX(LAMBDA(x, IF(x<>"", IFERROR(TEXT(x*1, "m/d/e"), x), ))
 (QUERY(QUERY(SPLIT(FLATTEN(Sheet1!A2:A&"|"&Sheet1!B2:B&"|"&Sheet1!C2:C 
 SEQUENCE(1, MAX(Sheet1!E2:E),0)&"|"&Sheet1!D2:D), "|",,), 
 "where Col3<=Col4 and Col4 is not null", ), 
 "select Col1,max(Col2) 
  where Col3*1>="&DATEVALUE("4/1/2022")&" 
  group by Col1 pivot Col3 label Col1''" )))

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 this sort 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 'Conventional solution' tab in the sample spreadsheet Matt kindly created.

Further, the recently introduced lambda functions let you create the whole result table with one formula, like this:

=lambda( 
  uniqueNames, names, tasks, startDates, dueDates, 
  lambda( 
    weeks, 
    makearray( 
      rows(uniqueNames), columns(weeks)   2, 
      lambda( 
        row, column, 
        lambda( 
          name, week, 
          lambda( 
            numTasksByName, filterTasksByWeek, 
            ifs( 
              (row = 1) * (column = 1), "Assignee", 
              (row = 1) * (column = 2), "Num tasks", 
              row = 1, week, 
              column = 1, name, 
              column = 2, numTasksByName, 
              true, iferror( textjoin( ", ", true, filterTasksByWeek) ) 
            ) 
          )(
             counta(iferror(filter(tasks, names = name))), 
             filter(tasks, names = name, startDates <= week, week <= dueDates) 
           ) 
        )( 
           index(uniqueNames, row - 1, 1), 
           index(weeks, 1, column - 2) 
         ) 
      ) 
    )
  )(sequence(1, (max(dueDates) - min(startDates)) / 7, min(startDates), 7))
)(unique(A2:A), A2:A, B2:B, C2:C, D2:D)

The benefit of this pattern is that it is fairly easy to adjust the contents of the table, and there is no need to copy formulas down when more data becomes available. The use of nested lambdas let us use readable names instead of range references within the various functions.

See the 'Lambda solution' tab in Matt's spreadsheet. The formula in cell G1 creates the whole table automatically.