Pivot view of data into another sheet

Anne77

I have organized my original sheet by Client name to show the multiple people (contact list) working on that job along with their start dates. I would like to create a second sheet to show organized by people all of jobs they are working on and when to see if they are overloaded during a timeframe. Sometimes the people could take on different roles (see "Steve" below) How can I pivot this information into a new sheet to capture all clients "Steve" is associated with regardless of where his name appears? And also include any new clients added to the original list or if a job is reassigned to "Steve" from someone else?

Original Sheet:

Desired new view for new sheet:

Really appreciate any help!

Paul Newcome

Oh. Sorry. I misunderstood what you were needing.

=IF(COUNT(CHILDREN(Job@row)) = 0, JOIN(COLLECT(Manager@row:Staff@row, Manager@row:Staff@row, @cell <> "")))

Edit to add: This can be applied as a column formula.

Paul Newcome

You would do a nested IF statement like so:

=IF(Supervisor@row <> "", "Supervisor", IF(Charging@row <> "", "Charging", ................))))

Then to get it to only apply to the child rows you would wrap the whole thing in another IF statement like so:

=IF(COUNT(CHILDREN(Job@row)) = 0, ......................................................................)

Paul Newcome

I would insert a helper column with this column formula in it:

=JOIN(COLLECT(Manager@row:Staff@row, Manager@row:Staff@row, @cell <> ""))

Then you can create a row report based on this sheet and group by the helper column.

Anne77

Thank you! Now, how can I have that apply only to the CHILD rows?

I tried =JOIN(COLLECT(CHILDREN(Manager@row:Staff@row)), (CHILDREN(Manager@row:Staff@row)), @cell <> "") but I get "incorrect arguement"

Paul Newcome

I'm not sure I follow. I don't see any indentation in either of your screenshots, so I don't know exactly where you are wanting the formula and what exactly you are wanting to pull.

Anne77

Sorry, I quickly created that example separate from my sheet so I wasn't using actual info. I have a parent row for Company A, Company X in a row above each of those groupings. So this repeat with the name scheduling out are the child rows beneath.

Paul Newcome

Are you able to provide a screenshot?

Anne77

Since all names roll up to the parent row, I want to exclude that row in the formula

Paul Newcome

Ah. Ok. Try this in the parent rows:

=JOIN(COLLECT(CHILDREN(), CHILDREN(), @cell <> ""))

Anne77

No, I want the parent row of my "Join" column to stay blank. Otherwise it returns a value of JeffAnneSteveLisa with the formula you gave directly above. I want to apply your first formula only to the child rows in the column and the cell of the parent row to stay blank.

My parent rows above already pull up the assignee to the row which is what I want, I just don't want them in the join column

This is what I get:

This is what I want with parent blank in join column - but all I did was delete the formula from the parent row. Is there a way to automate so if new Company's are added the formula will apply?

Sorry this is so complicated!

Paul Newcome

Oh. Sorry. I misunderstood what you were needing.

=IF(COUNT(CHILDREN(Job@row)) = 0, JOIN(COLLECT(Manager@row:Staff@row, Manager@row:Staff@row, @cell <> "")))

Edit to add: This can be applied as a column formula.

Anne77

Perfect! Thank you so much!!

Anne77

I'm trying to take this a step further and have another helper column identify the job title of the employee based on the column title their original name falls under. So if the Supervisor column is not blank, insert the word "Supervisor" into my helper column, etc. And have it only apply to the child rows like before. I tried this but not working:

=IF(COUNT(CHILDREN([SUPERVISOR]@row)) <>, "SUPERVISOR", IF(COUNT(CHILDREN(CHARGING@row)) <>, "CHARGING", IF(COUNT(CHILDREN(STAFF@row)) <>, "STAFF"

It returns as #unparseable. Are you able to help?

Paul Newcome

You would do a nested IF statement like so:

=IF(Supervisor@row <> "", "Supervisor", IF(Charging@row <> "", "Charging", ................))))

Then to get it to only apply to the child rows you would wrap the whole thing in another IF statement like so:

=IF(COUNT(CHILDREN(Job@row)) = 0, ......................................................................)

Anne77

Thank you!

Paul Newcome

Happy to help. 👍️