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

Alethea Pollack

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

Genevieve P.

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

Genevieve P.

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

Alethea Pollack

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.

Genevieve P.

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