How can I move children from primary task column into new columns in the parent row

Initially the team asked for all of our project entries to be in a single column, and common subgroups be indented. Later, management realized they wanted to be able to see the subgroups as columns. I've successfully matched the subgroup row name and move it into the new column but been unable to move the contents into the project's parent row. I've tried a series of helper columns

=IF(AND((MATCH([Level P]@row, ANCESTORS(Project@row), 0)), (MATCH("Architect", Project@row, 0))), VALUE([Joined CDetails]@row), " N/A")

but missing something , any suggestions?


Answers

  • James Keuning
    James Keuning ✭✭✭✭✭

    Maybe someone else will understand more clearly, but I do not understand... if you use a column with indentation, you can have an unlimited number of rows. But you want to pivot that out to columns? How do you know how many columns you need? Is every project the same in terms of the rows?

    Can you manually mock up current state and desired state and screen shot both? Then we can figure out if some formulas will get you there.

  • Sorry for the confusion. I'm a newbie to smart sheets.

    The project column includes a number of case studies and subsequent rows indented to reflect grouped characteristics. For example, the project name is a building and characteristics would be the architect, the type of building, the materials. Each characteristic was indented and then Child rows indented again below lists details with the row including comments and attachments if relevant.

    Each Project case study varies in length, and the same is true for the number of children for a given parent characteristic. here's s mock up of the single column

    PROJECT Comments

    44 Times Sq NYC photo.jpg

    Architect

    Kiss& Cathart Gold LEED

    Building Type

    with awnings

    exterior glass

    Materials

    laminated PV, Concrete, steel

    ..........

    The Desired sheet

    PROJECT IMAGE ARCHITECT BUILDING TYPE MATERIALS Comment

    44 Times sq, NYC photo.jpg Kiss&Cathart awnings, exerior glass laminated PV, Gold LEED

    In Excel, I managed to do a simple pivot table and though the new columns didn't fully align across each case they were reasonably close, and with modest manual adjustments, I was able to achieve the desired results. The problem is Excel doesn't retain the attachments that exist in the respective rows.

    In the interim, I've created a formula using descendants to compute the number of rows for each case, managed to figure out how to use the Join function to consolidate the children of a specific characteristic. What I can't figure out is moving the identified matching information into the project row

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 06/08/21

    Hi @Rachel Kaberon

    I hope you're well and safe!

    I'd be happy to take a quick look.

    Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, [email protected])

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!