Skip to main content
Web Applications

Return to Question

Edit for clarity. Put cell values in markdown
Source Link
Blindspots ♦
Blindspots ♦
  • 7.5k
  • 5
  • 20
  • 38

Filter the data of two different columnthat has relationships based solely on relative row position

I'm trying to filter down the data of two different columns. I have exported the JIRA Time Tracking Report from JIRA which consists ofdata in Google Sheets. The exported data includes the following columns:

Issue Type Key Status Priority Summary Original Time Estimated (min) Σ Estimated Time Remaining (min) Σ Total Time Spent (min) Σ Accuracy Σ Accuracy (%) Σ

I'm interested in filtering by both Issue Type and Key Status:

  • Key Status contains unique values such as EP-1234
  • Issue Type values can be duplicates and I'm generally interested in the following three:
    1. Story
    2. Sub-task
    3. Sub-bug

I'm having a problem filtering because in JIRA the Sub-task and Sub-bug values are associated with a Story but in the exported spreadsheetdata this relationship is communicated solely by relative row position withas Sub-task and Sub-bug belongingbelong to the Story that precedes them.

For example, in the table below all the Sub-task values are associated with the same Story EP-2643:

Issue Type Key Status
Story EP-2643
-> Sub-task EP-2717
-> Sub-task EP-2716
-> Sub-task EP-2715
-> Sub-task EP-2714
-> Sub-task EP-2713
-> Sub-task EP-2712

It isn't practical to associate the values manually. Is there a formula or script that would allow me toautomatically associate the Sub-task and Sub-bug values with the appropriate Story automatically? It isn't practical to do this manually.

Filter the data of two different column

I'm trying to filter down the data of two different columns. I have exported the Time Tracking Report from JIRA which consists of the following columns:

Issue Type Key Status Priority Summary Original Time Estimated (min) Σ Estimated Time Remaining (min) Σ Total Time Spent (min) Σ Accuracy Σ Accuracy (%) Σ

I'm interested in filtering by both Issue Type and Key Status:

  • Key Status contains unique values such as EP-1234
  • Issue Type values can be duplicates and I'm generally interested in the following three:
    1. Story
    2. Sub-task
    3. Sub-bug

I'm having a problem filtering because in JIRA the Sub-task and Sub-bug values are associated with a Story but in the exported spreadsheet this relationship is communicated solely by row position with Sub-task and Sub-bug belonging to the Story that precedes them.

For example, in the table below all the Sub-task values are associated with the same Story EP-2643:

Issue Type Key Status
Story EP-2643
-> Sub-task EP-2717
-> Sub-task EP-2716
-> Sub-task EP-2715
-> Sub-task EP-2714
-> Sub-task EP-2713
-> Sub-task EP-2712

Is there a formula or script that would allow me to associate the Sub-task and Sub-bug values with the appropriate Story automatically? It isn't practical to do this manually.

Filter data that has relationships based solely on relative row position

I'm trying to filter exported JIRA Time Tracking Report data in Google Sheets. The exported data includes the following columns:

Issue Type Key Status Priority Summary Original Time Estimated (min) Σ Estimated Time Remaining (min) Σ Total Time Spent (min) Σ Accuracy Σ Accuracy (%) Σ

I'm interested in filtering by Issue Type and Key Status:

  • Key Status contains unique values such as EP-1234
  • Issue Type values can be duplicates and I'm generally interested in the following three:
    1. Story
    2. Sub-task
    3. Sub-bug

I'm having a problem filtering because in JIRA the Sub-task and Sub-bug values are associated with a Story but in the exported data this relationship is communicated solely by relative row position as Sub-task and Sub-bug belong to the Story that precedes them.

For example, in the table below all the Sub-task values are associated with the same Story EP-2643:

Issue Type Key Status
Story EP-2643
-> Sub-task EP-2717
-> Sub-task EP-2716
-> Sub-task EP-2715
-> Sub-task EP-2714
-> Sub-task EP-2713
-> Sub-task EP-2712

It isn't practical to associate the values manually. Is there a formula or script that automatically associate the Sub-task and Sub-bug values with the appropriate Story?

Edit for clarity. Put cell values in markdown
Source Link
Blindspots ♦
Blindspots ♦
  • 7.5k
  • 5
  • 20
  • 38
Issue Type  Key Status  Priority    Summary Original Time Estimated (min)   Σ   Estimated Time Remaining (min)  Σ   Total Time Spent (min)  Σ   Accuracy    Σ   Accuracy (%)    Σ
Issue TypeKey StatusPrioritySummaryOriginal Time Estimated (min) ΣEstimated Time Remaining (min) ΣTotal Time Spent (min) ΣAccuracy ΣAccuracy (%) Σ

I'm interested in filtering downby both Issue Type and Key Key Status. Key Status is unique to each row e.g. EP-1234:

  • Key Status contains unique values such as EP-1234
  • Issue Type values can be duplicates and I'm generally interested in the following three:
    1. Story
    2. Sub-task
    3. Sub-bug

Issue Type includes several value types, but I'm generally interestedhaving a problem filtering because in three: story, sub-task and sub-bug.

It would be quite easy for me to narrow downJIRA the stories (thereSub-task and Sub-bug values are about 10 inassociated with a given sprint,Story but each story has a large number of sub-tasks and sub-bugs directly below in the column Issue Type for example:exported spreadsheet this relationship is communicated solely by row position with Sub-task and Sub-bug belonging to the Story that precedes them.

Issue Type.  Key Status

Story        EP-2643
-> Sub-task  EP-2717
-> Sub-task  EP-2716
-> Sub-task  EP-2715
-> Sub-task  EP-2714
-> Sub-task  EP-2713
-> Sub-task  EP-2712

Both stories andFor example, in the table below all the -> Sub-tasksSub-task values are located inassociated with the same column and other than an order there is no connection between the story EP-2643 and it's sub-tasks.Story EP-2643:

Issue TypeKey Status
StoryEP-2643
-> Sub-taskEP-2717
-> Sub-taskEP-2716
-> Sub-taskEP-2715
-> Sub-taskEP-2714
-> Sub-taskEP-2713
-> Sub-taskEP-2712

Is there any filtera formula or query,or script that would allow me to somehow pair this dataassociate the Sub-task and Sub-bug values with the appropriate Story automatically? I can do it manually of course, but if I haveIt isn't practical to do it everyday, it won't be efficientthis manually.

Issue Type  Key Status  Priority    Summary Original Time Estimated (min)   Σ   Estimated Time Remaining (min)  Σ   Total Time Spent (min)  Σ   Accuracy    Σ   Accuracy (%)    Σ

I'm interested in filtering down both Issue Type Key Status. Key Status is unique to each row e.g. EP-1234

Issue Type includes several value types, but I'm generally interested in three: story, sub-task and sub-bug.

It would be quite easy for me to narrow down the stories (there are about 10 in a given sprint, but each story has a large number of sub-tasks and sub-bugs directly below in the column Issue Type for example:

Issue Type.  Key Status

Story        EP-2643
-> Sub-task  EP-2717
-> Sub-task  EP-2716
-> Sub-task  EP-2715
-> Sub-task  EP-2714
-> Sub-task  EP-2713
-> Sub-task  EP-2712

Both stories and -> Sub-tasks are located in the same column and other than an order there is no connection between the story EP-2643 and it's sub-tasks. Is there any filter or query,or script that would allow me to somehow pair this data automatically? I can do it manually of course, but if I have to do it everyday, it won't be efficient.

Issue TypeKey StatusPrioritySummaryOriginal Time Estimated (min) ΣEstimated Time Remaining (min) ΣTotal Time Spent (min) ΣAccuracy ΣAccuracy (%) Σ

I'm interested in filtering by both Issue Type and Key Status:

  • Key Status contains unique values such as EP-1234
  • Issue Type values can be duplicates and I'm generally interested in the following three:
    1. Story
    2. Sub-task
    3. Sub-bug

I'm having a problem filtering because in JIRA the Sub-task and Sub-bug values are associated with a Story but in the exported spreadsheet this relationship is communicated solely by row position with Sub-task and Sub-bug belonging to the Story that precedes them.

For example, in the table below all the Sub-task values are associated with the same Story EP-2643:

Issue TypeKey Status
StoryEP-2643
-> Sub-taskEP-2717
-> Sub-taskEP-2716
-> Sub-taskEP-2715
-> Sub-taskEP-2714
-> Sub-taskEP-2713
-> Sub-taskEP-2712

Is there a formula or script that would allow me to associate the Sub-task and Sub-bug values with the appropriate Story automatically? It isn't practical to do this manually.

Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
Bumped by Community user
deleted 57 characters in body
Source Link
serenesat
serenesat
  • 10k
  • 30
  • 33
  • 50

I'm trying to filter down the data of two different columns. I have exported the Time Tracking Report from JiraJIRA which consists of the following columns:

Issue Type Key Status Priority Summary Original Time Estimated (min) Σ Estimated Time Remaining (min) Σ Total Time Spent (min) Σ Accuracy Σ Accuracy (%) Σ

Issue Type  Key Status  Priority    Summary Original Time Estimated (min)   Σ   Estimated Time Remaining (min)  Σ   Total Time Spent (min)  Σ   Accuracy    Σ   Accuracy (%)    Σ

I'm interested in filtering down both Issue Type Key StatusIssue Type Key Status. Key Status is unique to each row e.g. EP-1234

Issue Type includes several value types, but I'm generally interested in three: story, sub-task and sub-bug.

It would be quite easy for me to narrow down the stories (there are about 10 in a given sprint, but each story has a large number of sub-tasks and sub-bugs directly below in the column Issue Type for example:

Issue Type.  Key Status

Story        EP-2643
-> Sub-task  EP-2717
-> Sub-task  EP-2716
-> Sub-task  EP-2715
-> Sub-task  EP-2714
-> Sub-task  EP-2713
-> Sub-task  EP-2712

Both stories and -> Sub-tasks are located in the same column and other than an order there is no connection between the story EP-2643 and it's sub-tasks. Is there any filter or query,or script that would allow me to somehow pair this data automatically? I can do it manually of course, but if I have to do it everyday, it won't be efficient.

Thanks for looking into an issue

I'm trying to filter down the data of two different columns. I have exported the Time Tracking Report from Jira which consists of the following columns:

Issue Type Key Status Priority Summary Original Time Estimated (min) Σ Estimated Time Remaining (min) Σ Total Time Spent (min) Σ Accuracy Σ Accuracy (%) Σ

I'm interested in filtering down both Issue Type Key Status. Key Status is unique to each row e.g. EP-1234

Issue Type includes several value types, but I'm generally interested in three: story, sub-task and sub-bug.

It would be quite easy for me to narrow down the stories (there are about 10 in a given sprint, but each story has a large number of sub-tasks and sub-bugs directly below in the column Issue Type for example:

Issue Type.  Key Status

Story        EP-2643
-> Sub-task  EP-2717
-> Sub-task  EP-2716
-> Sub-task  EP-2715
-> Sub-task  EP-2714
-> Sub-task  EP-2713
-> Sub-task  EP-2712

Both stories and -> Sub-tasks are located in the same column and other than an order there is no connection between the story EP-2643 and it's sub-tasks. Is there any filter or query,or script that would allow me to somehow pair this data automatically? I can do it manually of course, but if I have to do it everyday, it won't be efficient.

Thanks for looking into an issue

I'm trying to filter down the data of two different columns. I have exported the Time Tracking Report from JIRA which consists of the following columns:

Issue Type  Key Status  Priority    Summary Original Time Estimated (min)   Σ   Estimated Time Remaining (min)  Σ   Total Time Spent (min)  Σ   Accuracy    Σ   Accuracy (%)    Σ

I'm interested in filtering down both Issue Type Key Status. Key Status is unique to each row e.g. EP-1234

Issue Type includes several value types, but I'm generally interested in three: story, sub-task and sub-bug.

It would be quite easy for me to narrow down the stories (there are about 10 in a given sprint, but each story has a large number of sub-tasks and sub-bugs directly below in the column Issue Type for example:

Issue Type.  Key Status

Story        EP-2643
-> Sub-task  EP-2717
-> Sub-task  EP-2716
-> Sub-task  EP-2715
-> Sub-task  EP-2714
-> Sub-task  EP-2713
-> Sub-task  EP-2712

Both stories and -> Sub-tasks are located in the same column and other than an order there is no connection between the story EP-2643 and it's sub-tasks. Is there any filter or query,or script that would allow me to somehow pair this data automatically? I can do it manually of course, but if I have to do it everyday, it won't be efficient.

Source Link
Michal
Michal
  • 1