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

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? 😢

Mark Cronk

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

BESP10

Thank you Mark, that worked. AWESOME!

Mark Cronk

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

Andrée Starå

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!

BESP10

Thank you for your help here attached.

On Board Sheet.pdf

Mark Cronk

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

Andrée Starå

@BESP10

Happy to help!

I saw that Mark answered already!

Let me know if I can help with anything else!

Best,

Andrée 

BESP10

Thank you Mark, that worked. AWESOME!

BESP10

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

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

BESP10

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

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

BESP10

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