Forum Discussion
Sorting Dewey codes in Excel
Well, a prior question would have to be "How do you want them sorted?" On the basis of the numeric values; alphabetically, on the basis of the letters; some hybrid of your own design?
What happens now when you sort them by all those different methods you mention? What about each of those is falling short of what you want?
We need to answer these questions--to define the sort you desire--before coming up with the satisfactory method. I realize you probably think the answer is obvious, given the reference to the Dewey (decimal?) code, but not all of us are versed in that coding system, even if we do know a lot about a lot of books.
The way to do it with Data | Sort would be to create a custom list of DD codes and have Excel sort on this instead of A-Z or Z-A. Custom lists are limited to 255 characters and I'll venture to say this would be a problem with your data set.
The other way to sort is using dynamic arrays. With a standard list of DD codes you could use something like this:
=LET(
i, --TEXTBEFORE(DD, " "),
a, TEXTAFTER(DD, " "),
SORTBY(DD, i, 1, a, 1)
)I tried to break it by adding dummy DD codes that shared the same number prefix as those already listed and it held up. You may want to share a sample workbook if your data contains anomalies (e.g. cells with only numbers and no text).
I'd suggest to modify a bit
=LET( i, --TEXTBEFORE(DD," ",,,1), a, TEXTAFTER(DD," ",,,,""), SORTBY(DD,i,1,a,1) & "" )to fix for the records where we have numbers only.
- Thank you for spending time helping to sort this!
- Thank you for spending time helping to sort this!
