How to List Parents By Common Child

Richn

I am working on a project book of work and currently have a list of resources listed by project:

Project 1:

• Rich
• John
• Angela
• Carly
• Ted

Project 2:

• Tim
• Jeff
• Rich
• Ted

Project 3:

• Sam
• Tim
• Ted

I am looking to create another sheet that will automatically pull from this first sheet and show the resource and the projects that they are in:

Rich

• Project 1
• Project 2

Ted

• Project 1
• Project 2
• Project 3

Angela

• Project 1

How could I automatically pull the parent information where a resource is listed multiple times. Any help will be appreciated.

Thanks!

Bassam Khalil

Hi @Richn

Hope you are fine, Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Or if you like me to fix the formula directly on your sheet please share me as an admin on a sample copy of your sheets ( Source & Destination ) and i will Create the exact formula for you then you can copy it to your original sheet.

My Email for sharing : Bassam.k@mobilproject.it

Genevieve P.

Hi @Richn

Can I clarify, is your first example above one sheet that has each Project listed as a Parent Row, with the resources indented below it (on child rows)?

If so, we will just need to add in one helper column to this main source sheet. This column will auto-populate the Project Name from the Parent row into each of the Child Rows using a column formula. Try something like this:

=PARENT([Primary Column]@row)

This is assuming your Project title is listed in the Primary column.

Then once you've turned this into a column formula, you can either create a Report and Group this Report by Resource, or you could use a JOIN(COLLECT cross-sheet formula to list every project associated with each resource in one cell.

Let me know if you'd like to see screen capture examples of any of the above information and I'm happy to explain further!

Cheers,

Genevieve

Richn

Hi Genevieve,

That is correct, the projects are listed in one sheet as parent rows with resources below as child rows.

Screen captures would be great if you could!

Genevieve P.

Hi @Richn

No problem!

Here is the Helper Column showing the formula I used to bring down the Project Name to each individual row. You can hide this column once you've created it:

Then I would create a Report, excluding any Parent Rows by filtering, then and Group by the resources:

I've collapsed some of the top resources as they only have 1 project assigned, but you can see that Ted shows each of his projects listed individually. This is because we pulled the Parent row information down into each child row so it can be displayed in the Report.

Will this work for you?

Cheers,

Genevieve

Richn

Thanks Genevieve! This worked. One more question- how do I filter out the parent row in the report?

Genevieve P.

Hi @Richn

I'm glad this worked for you!

In my example, you'll see that every Parent row is blank in the Helper Column. I used the filter to say that column "Is not blank", does that make sense?

Cheers,

Genevieve