Index/Collect/Match help

Bruce Johnson

Hi

I have a sheet that has epics, stories, tasks and I want to extract only the epics to another sheet.

layout is simple

Name IssueType

Epic 1 Epic

Story 1 Story

Story 2 Story

Task 1 Task

Task 2 Task

Epic 2 Epic

Story 3 Story

Story 4 Story

Task 3 Task

Task 4 Task

I want the other sheet to only have

Epic 1

Epic 2

thanks so much.

L_123

=index(collect(summary:summary,epicyes:epicyes,contains("epic",lower(@cell))),1)

will return the summary value for the first epic. change the 1 to any value (or use a count() to automate) to return X value. (you can also switch the ranges to other sheet references to move this to a second sheet.

alternatively you can use a copy or move workflow with a helper checkbox to move all of the row information to a new sheet.

L_123

something like

=if(not(isblank(epicname@row)),"Epic"+count(epicyes$1:epicyes1),"")

in the second column, then drag down?

Or do you want to start with a special number/actually reference the previous submission

Genevieve P.

Hi @Bruce Johnson

The easiest way to do this would actually be to create a Report that filters down to just the Epics, would that be a possibility in your scenario? (See here for more information.)

Otherwise, you could use an INDEX(MATCH cross-sheet formula, but it would need a unique identifier per-row to find each match and bring through the corresponding epic.

For example, if you used an Index(Match on your current set up with just the 2 columns, it would look like this:

=INDEX({Name Column in other sheet}, MATCH("Epic", {Issue Type Column}))

However this is just looking for one criteria (that the Match is "Epic") so it will return "Epic 1" since that's the first match it will find. When you add this same formula to the cell below, you'll return "Epic 1" again, instead of now "Epic 2" as there are no unique identifiers for this formula to find and bring back - it will always find just the first match.

Are you able to share a bit more about the difference between the two sheets? (Screen captures of each sheet would be preferable, but only if you're able to block out any sensitive data). It would also be helpful to understand the reason and process behind bringing the Epics over... for example, do you need the cells to be linked so that they update if the source updates? (If not, you could set up a Copy Row automation whenever a new Epic type is added.)

Hope this helps,

Genevieve

Bruce Johnson

Hi Genivieve

Thank you for the reply. The Report aspect is easy and I was thinking that - BUT - here is what I am trying to do.

I have a project with 20ish different workstreams - each will have it's own/different IssueType = Epics/Stories/Tasks/Sub-Tasks. We currently have many metrics - gathering - statistics for open/closed/inprogress etc at the macro level and on a per assignee basis. What we want is to create a sheet that eventually will end up in a report that for each Epic what is the status of all children issuetype. What I need to do isextract the name of the Epic on each sheet and then determine how many are in planning, inprogress, code review, validation, done. In addition, we would continue to create formulas for the number of story points, no points, unassigned etc. these would then roll into a report and we could then dashboard the results for each workstream. Creating these formulas is easy but I need to ectract out to the second sheet just the Epic names as a start to interogate each of those Epics. Thanks so much.

Screenshot 2020-09-21 at 9.48.29 AM.png

L_123

=index(collect(summary:summary,epicyes:epicyes,contains("epic",lower(@cell))),1)

will return the summary value for the first epic. change the 1 to any value (or use a count() to automate) to return X value. (you can also switch the ranges to other sheet references to move this to a second sheet.

alternatively you can use a copy or move workflow with a helper checkbox to move all of the row information to a new sheet.

Bruce Johnson

Hi L@123

Thank you for the reply. I like the formula and it will most likely work - BUT - the difficulty is automating the return X value.

I want to automate that but in looking a the sheet I sent, the Epic name has a variance between the Epics due to Story, Task and Sub-Task names in between those names - I tried playing around with count children and that gets complicated and I need to know where the start and end of each epic - which is variable - the epic may have 3 stories or 10 stories and each of those could have 1 task or 10 tasks - To get a consecutive unique number 1, 2, 3 for each Epic name gets complicated - so looking at the sheet previous -

Run-ANLY-VNA-Marcom-Qualtircs - has 13 children, sub-children etc.

Run-ANLY-VNA-Marcom-Hubspot - has zero

Run-Data-VNA-ENT-Tidemark - has 10 children, sub-children etc.

What I would like to end up with is a simple grid that shows the status of each Epic as such

Screenshot 2020-09-21 at 11.05.14 AM.png

Seems like it should be easy but it seems that it gets complicated with real world circumstances. Any help, guidance is very appreciated.

L_123

Do you have a column showing the status of each epic? If so you can use a countifs

=countifs(epicyes:epicyes,contains("epic",lower(@cell)),status:status,[total backlog]$1,summary:summary,[Epic Name]@row

Looking into your naming schema a little more, it seems like you might need a helper column.

=if(count(parent(summary@row))>0,parent(summary@row),"")

which you can then use to reference for your countifs() to make sure you are referencing the correct name.

Bruce Johnson

Thanks @L@123

yes eventually I want to interogate other columns for status and the like and I have that - I don't want to create a bunch of columns in the main sheet - I could if there is no other alternative - BUT - I wanted to see if there was a way to extract only the EPIC names to another sheet so I could have that sheet look up and countif off the main sheet - of course I could create formulas in the main then run a rport against those - but lots of people will be adding stories, tasks and sub-tasks to the epics and chances are - out of the 100's of people using these sheets - someone or many will overwrite, change, delete, screw up the sheet - which is why I want a separate sheet - sheet summary won't work - I don't know the epic names and that would have to be added for each workstream. So simply put - what is needed to create a unique name as a match for a non-contiguous column of Epic, Story, task and sub-task under IssueType - where I only want it to count if it = Epic. and given your formla from previous messgae a sheet that extracts the Epic Name under the summary column and helper columns I would expect to have to use them.

thank you very much

L_123

I mean... if you do a column reference you can filter it and post values to another sheet using index(collect(),count()) then you can use other formula referencing the filtered data if you want.

Bruce Johnson

Hi @L@123

Thanks for the reply - let's forget about index(collect - that I can now get working thanks for your help.

What is not evident is getting the Epic name to increase by 1 - so that I have a unique name - the spacing of those in the helper column is random - again - some epics have more stories, tasks, subtasks than others - so I need a way to add 1 to the previous epic name (epic1) in that column so the next Epic will be named Epic 2 - so on and so forth.

tyvm

L_123

something like

=if(not(isblank(epicname@row)),"Epic"+count(epicyes$1:epicyes1),"")

in the second column, then drag down?

Or do you want to start with a special number/actually reference the previous submission

Bruce Johnson

that did the trick - just had to start the epiceyes$1:epiceyes at the first instance of epiceyes. thanks so much for the help - really appreciated that you hung in there.