Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Sorting heirarchy numbers (1.1, 1.1.1, 1.2) in reports
Hi everyone,
I am using reports to merge 20 to 30 sheets and sorting them on multiple and different heirarchy columns (containing numbers such as 1.1, 1.1.1, 1.2). This doesn't really work out of the box because the report sort is apparently just a standard natural sort and doesn't recognize this notation.
I wasn't able to find a existing solution for this that didn't require manual effort, so here is a formula creates a sortable number in a new column based on the heirachy number.
In the screenshot below, the column on the left shows the normal sort behavior. The column on the right is the result of the formula and I can sort on this column to have the primary column appear sorted correclty. The formula also works correctly with or without trailing periods and with numbers between 0 and 999.999.999, but for now, only with a maximum of three levels.
The formula, if you would like to play with it:
=VALUE(IF(FIND(".", IF(FIND(".", [Primary Column]1) > 0, RIGHT([Primary Column]1, LEN([Primary Column]1) - FIND(".", [Primary Column]1)), "")) > 0, RIGHT(IF(FIND(".", [Primary Column]1) > 0, RIGHT([Primary Column]1, LEN([Primary Column]1) - FIND(".", [Primary Column]1)), ""), LEN(IF(FIND(".", [Primary Column]1) > 0, RIGHT([Primary Column]1, LEN([Primary Column]1) - FIND(".", [Primary Column]1)), "")) - FIND(".", IF(FIND(".", [Primary Column]1) > 0, RIGHT([Primary Column]1, LEN([Primary Column]1) - FIND(".", [Primary Column]1)), ""))), "")) + VALUE(LEFT(IF(FIND(".", [Primary Column]1) > 0, RIGHT([Primary Column]1, LEN([Primary Column]1) - FIND(".", [Primary Column]1)), ""), FIND(".", IF(FIND(".", [Primary Column]1) > 0, RIGHT([Primary Column]1, LEN([Primary Column]1) - FIND(".", [Primary Column]1)), "") + ".") - 1)) * 1000 + VALUE(LEFT([Primary Column]1, FIND(".", [Primary Column]1 + ".") - 1)) * 1000000
If you use this on a column with a different name, you will need to use a text editor, like notepad, to do a find and replace so you can replace [Primary Column]1 with the [name_of_your_column]1. It seems like it works best if you keep the new column in square brackets.
Once you have put the formula in the first row of your sheet, use the fill handle to fill the formula down for the rest of your rows, so the row numbers referenced by the formula wil be correct.
Good luck!
DJ
Comments
-
Wow, great formula and workaround! Thanks!
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.8K Get Help
- 376 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 284 Events
- 33 Webinars
- 7.3K Forum Archives