Create a sprint report based on column values by repeating the row

Hi Community!

I have a sheet with distinct rows representing a task and a column indicating various design/build activities by sprint. I need to summarize this data into a new report or sheet to see all the work per sprint. I don't need any other columns/dates autopopulated.

Source sheet sample

Desired report sample


Best Answer

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Alethea Pollack

    Based on your source sheet set-up, you would need to evaluate each column individually to see if it contains that specific Sprint number (ex. "3.4"), and then return the Dependency for that row from that column if it found a match.

    You could do this with a JOIN(COLLECT formula, however this won't be able to parse multiple values down your column into new rows, it would JOIN together all the Dependencies into one cell.

    You would need multiple JOIN(COLLECT formulas, one per column, like so:

    =JOIN(COLLECT({Dependencies Column}, {Column 1}, 3.4), ", ") + "', " + JOIN(COLLECT({Dependencies Column}, {Column 2}, 3.4), ", ") + "', " + JOIN(COLLECT({Dependencies Column}, {Column 3}, 3.4), ", ") ... etc


    An alternative would be to set up all possible rows per-sprint in one sheet, then use a COUNTIFS formula to check each column to see if that value appears for that Dependency, and if it does, check a box.

    =IF(Parent@row = "", "", IF(SUM(COUNTIFS({Dependency}, [Task Sprint Name]@row, {1st Column}, Parent@row), COUNTIFS({Dependency}, [Task Sprint Name]@row, {Column 2}, Parent@row), COUNTIFS({Dependency}, [Task Sprint Name]@row, {3rd Column}, Parent@row)) > 0, 1, 0))

    Then you could use a Report to Group by the Parent / Sprint Column and filter out any of the rows that are not checked.

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee
    Answer ✓

    Hi @Alethea Pollack

    Based on your source sheet set-up, you would need to evaluate each column individually to see if it contains that specific Sprint number (ex. "3.4"), and then return the Dependency for that row from that column if it found a match.

    You could do this with a JOIN(COLLECT formula, however this won't be able to parse multiple values down your column into new rows, it would JOIN together all the Dependencies into one cell.

    You would need multiple JOIN(COLLECT formulas, one per column, like so:

    =JOIN(COLLECT({Dependencies Column}, {Column 1}, 3.4), ", ") + "', " + JOIN(COLLECT({Dependencies Column}, {Column 2}, 3.4), ", ") + "', " + JOIN(COLLECT({Dependencies Column}, {Column 3}, 3.4), ", ") ... etc


    An alternative would be to set up all possible rows per-sprint in one sheet, then use a COUNTIFS formula to check each column to see if that value appears for that Dependency, and if it does, check a box.

    =IF(Parent@row = "", "", IF(SUM(COUNTIFS({Dependency}, [Task Sprint Name]@row, {1st Column}, Parent@row), COUNTIFS({Dependency}, [Task Sprint Name]@row, {Column 2}, Parent@row), COUNTIFS({Dependency}, [Task Sprint Name]@row, {3rd Column}, Parent@row)) > 0, 1, 0))

    Then you could use a Report to Group by the Parent / Sprint Column and filter out any of the rows that are not checked.

    Cheers,

    Genevieve

  • Thank you! I tried out your formula and it did work but did not give me exactly what I needed. I ended up adding a sheet summary field and linking it to an "IF" formula to filter the column results by sprint and then created a sheet filter to show me all rows that did not contain blanks.

  • I'm glad you were able to figure out something that works for you! 🙂