Sorting heirarchy numbers (1.1, 1.1.1, 1.2) in reports

DJ Dates

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

JohnHinkle

Wow, great formula and workaround! Thanks!