Last week, Elaine asked:
“I have project numbers that are both numeric and text which follow a parent and child relationship. The problem is when I sort by project number, all the numbers are sorted at the top, and all the text at the bottom. How do I sort, but keep parent projects with the child projects?”
So Elaine would like to see something like the table shown here – all projects sorted so that the numeric parent stays with the textual children.
The problem is that Excel doesn’t sort this way by default. If I were to sort my project table in the standard ‘Sort Ascending’ fashion, I would get this result. Notice how all the numbers float to the top.
The fix for this kind of an issue is simple, but a bit annoying. Annoying because you’re forced to create a helper column when all you want to do is sort.
Create a new column called SortKey. There, you will enter a formula to show all the values in the original column, but in a textual format.
In this case, I entered TEXT(A2, “###”) in the first cell of my new column, then copied the formula down.
Start a sort on your newly created SortKey column.
Excel will ask how you want the numbers treated during the sort. Select the option that says:
‘Sort numbers and numbers stored at text separately’
The final step is to delete or hide your SortKey column (depending on whether you’ll need to continuously sort the data).
And there you have it.
Well Elaine, that’s my answer.
If any of you gurus know an easier way to handle this kind of sorting, I would love to hear it.