Sorting Parent Rows has messed up the order of my child rows

Options
BESP10
BESP10 ✭✭✭✭✭✭

Good afternoon, I have done something I feel is going to haunt me! I have a sheet with 367 parent rows, there is a parent row followed by 5 child rows. The order is supposed to be Parent Row then Drawing>Fabrication Drawing>Fabrication>Delivery>Erection.

However I collapsed the rows so just the parent rows are showing and sorted the column in Ascending Order so my parent rows were in order. Now all of my child rows are out of order in this order Approval Drawing>Delivery>Erection>Fabircation Drawing>Fabrication.

I desperately need to fix this and get it back to the order it is supposed to be in, can someone please advise? 😢

Best Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @BESP10 ,

    Fortunately your child rows have different names. Add a text/number column [Sort] and enter the formula:

    =IF([Item List]@row = "Approval Drawings", 1, IF([Item List]@row = "Fabrication Drawings", 2, IF([Item List]@row = "Fabrication", 3, IF([Item List]@row = "Delivery", 4, IF([Item List]@row = "Erection",4, 0)))))

    The child rows should populate with a number 1-4 and the parent rows should be 0. Sort the Sort Column ascending.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • BESP10
    BESP10 ✭✭✭✭✭✭
    Answer ✓
    Options

    Thank you Mark, that worked. AWESOME!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @BESP10 ,

    When you use CONTAINS you don't need to worry about the "-". CONTAINS looks for the string in the cell and returns true or false. The formula below should work for you

    =IF(CONTAINS("Approval Drawings", [Scope]@row)=1, 1, IF(CONTAINS("Fabrication Drawings", scope@row)=1, 2, IF(CONTAINS("Fabrication", scope@row)=1, 3, IF(CONTAINS("Delivery", scope@row )=1, 4, IF(CONTAINS("Erection", scope@row)=1, 5, 0)))))

    Based on the formula, your Scope Column should contain the text string. Is that correct?

    Hope it works.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    Hi @BESP10

    Maybe you can use the created column if each row was created in order.

    Can you maybe share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

    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 help the Community by marking it as the accepted answer/helpful. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • BESP10
    BESP10 ✭✭✭✭✭✭
    Options

    Thank you for your help here attached.



  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @BESP10 ,

    Fortunately your child rows have different names. Add a text/number column [Sort] and enter the formula:

    =IF([Item List]@row = "Approval Drawings", 1, IF([Item List]@row = "Fabrication Drawings", 2, IF([Item List]@row = "Fabrication", 3, IF([Item List]@row = "Delivery", 4, IF([Item List]@row = "Erection",4, 0)))))

    The child rows should populate with a number 1-4 and the parent rows should be 0. Sort the Sort Column ascending.

    Work?

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    @BESP10

    Happy to help!

    I saw that Mark answered already!

    Let me know if I can help with anything else!

    Best,

    Andrée 

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • BESP10
    BESP10 ✭✭✭✭✭✭
    Answer ✓
    Options

    Thank you Mark, that worked. AWESOME!

  • BESP10
    BESP10 ✭✭✭✭✭✭
    Options

    HI @Mark Cronk


    The formula worked, thanks, am I able to add an "Or" to this? (does it exist?) some of my chilkd rows have a - in them and some dont

    No dash

    dash


  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Hi BESP10,

    Yes, you could use an OR() if there are variations. You could also use CONTAINS() which would be less typing.

    =IF(CONTAINS("Approval Drawings", [Item List]@row)=1, 1, IF(CONTAINS("Fabrication Drawings", [Item List]@row)=1, 2, IF(CONTAINS("Fabrication", [Item List]@row)=1, 3, IF(CONTAINS("Delivery", [Item List]@row )=1, 4, IF(CONTAINS("Erection", [Item List]@row)=1, 5, 0)))))

    Happy to help,

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • BESP10
    BESP10 ✭✭✭✭✭✭
    Options

    Hi @Mark Cronk

    So for my two different items I need to sort by I tried this and it gives me an error. Sorry I did not get this right but it does not look like the correct formula?

    =IF(CONTAINS("Approval Drawings", "-Approval Drawings",Scope@row)=1, 1, IF(CONTAINS("Fabrication Drawings", +-Fabrication Drawings", Scope@row)=1, 2, IF(CONTAINS("Fabrication", "-Fabrication",Scope@row)=1, 3, IF(CONTAINS("Delivery", "-Delivery",Scope@row )=1, 4, IF(CONTAINS("Erection","-Erection",Scope@row)=1, 5, 0)))))

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @BESP10 ,

    When you use CONTAINS you don't need to worry about the "-". CONTAINS looks for the string in the cell and returns true or false. The formula below should work for you

    =IF(CONTAINS("Approval Drawings", [Scope]@row)=1, 1, IF(CONTAINS("Fabrication Drawings", scope@row)=1, 2, IF(CONTAINS("Fabrication", scope@row)=1, 3, IF(CONTAINS("Delivery", scope@row )=1, 4, IF(CONTAINS("Erection", scope@row)=1, 5, 0)))))

    Based on the formula, your Scope Column should contain the text string. Is that correct?

    Hope it works.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • BESP10
    BESP10 ✭✭✭✭✭✭
    Options

    GREAT. I see that now. Huge help. Thanks!