Dewey Decimal System - sort in excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I am trying to sort data in an excel document using the dewey decimal
system (0000.00.00.00). For some reason the sort is not working properly. I
have tried to format the cells several different ways and I have also
selected different options under "international" and, thus far, can find
nothing to work. If anyone has any information or suggestions on doing this,
it would be greatly appreciated. Thank you.
 
Maybe you could give us half-a-dozen DDS numbers and tell how they failed to
sort for you
best wsihes
 
Lucy,

Excel sorts numbers in numerical order... 3 comes before 22.
Excel sorts text character by character... a3 comes after a22

A "number" with multiple decimal points is considered text.
So 0000.00.00.00 is sorted character by character.

The Dewey Decimal System is unusual in that the first group
of numbers is arranged (sorted) numerically, while subsequent
groups are arranged (sorted) character by character.
Also, library classification systems use prefix characters to designate
topic/size which would affect the sort.
--
Jim Cone
San Francisco, USA
http://www.officeletter.com/blink/specialsort.html




"Lucy" <[email protected]>
wrote in message
Hello,
I am trying to sort data in an excel document using the dewey decimal
system (0000.00.00.00). For some reason the sort is not working properly. I
have tried to format the cells several different ways and I have also
selected different options under "international" and, thus far, can find
nothing to work. If anyone has any information or suggestions on doing this,
it would be greatly appreciated. Thank you.
 
The problem that you're going to run into trying to do this in Excel is Excel
doesn't know how to sort this data. It's not conventional text, and so it
can't be alphabetized, and it's not numbers, so it can't be sorted from least
to greatest.

Perhaps one possibility is to extract from the Dewey Decimal Number the
relevant criteria you want to sort by, in a helper column, and sort your
table on that column.

Possibly a pivot table could work.

Dave
 
Select the column containing the IDs. Then pull-down:
Data > Text to columns.. > Delimited and then select the period as the
delimiting character. Make sure you tell the Wizard to use the next column
over, so the original data will not be over-written.

You will now have the original column and 4 "helper" columns. Use the
"helper" columns for sorting.
 
Here is how you might sort the faceted elements,
the helper columns of Gary's post,
with a Pivot Table that Dave mentioned.
Assume the faceted elements look like this:

bin1 bin2 bin3 bin4 bin5 bin6
1 1 5 7 8 9
1 1 5 8
3 4 8 3 2 1
4 1 3
2 1 3
4 1 3 1 1 6
4
1 1 5 7 9
5
4 1
3 9 3 7
3 9 2 4
5 8 8 7 2 1
6 6 9 6
5 6 9 8 2

Add headers to the table and fill in the blanks
with a small number, say 0.1
After dragging bin1 to bin6 into ROW and
unchecking all the subtotals, the Pivot Table,
located at A1, might look like this:

Count of bin1
bin1 bin2 bin3 bin4 bin5 bin6 Total
1 1 5 7 8 9 1
9 0.1 1
8 0.1 0.1 1
2 1 3 0.1 0.1 0.1 1
3 4 8 3 2 1 1
9 2 4 0.1 0.1 1
3 7 0.1 0.1 1
4 0.1 0.1 0.1 0.1 0.1 1
1 0.1 0.1 0.1 0.1 1
3 0.1 0.1 0.1 1
1 1 6 1
5 0.1 0.1 0.1 0.1 0.1 1
6 9 8 2 0.1 1
8 8 7 2 1 1
6 6 9 6 0.1 0.1 1

At H3 enter this formula and
fill an area the same size as the PT:
=IF(ISBLANK(A3),H2,IF(A3=0.1,"",A3))
Concatenate the rows to get this sorted list:
115789
11579
1158
213
348321
3924
3937
4
41
413
413116
5
56982
588721
6696
 
Hi Lucy,
Assume you are still stuck, as you've not replied back as
to any specific solution that worked for you, and the very
first reply asked for some sample data, specifically that you
had a problem with. If we don't know what you got and what
you expected it would be difficult to guess why something is
wrong. You are going to have to create a helper column, suggest
something like 4 digits, "x" instead of
decimal point to insure value is text, 4 more digits, " " space,
remainder of text which I think would be all alphabetic.
Take a look at
http://www.mvps.org/dmcritchie/excel/sorttcp.htm
to provide some hints. You need to put segments into
fixed positions. ASCII data sorts digits before letters
and Excel sorts cells that are numeric before cells that
are not. Excel really has it's own collating sequence.
Also see
http://www.mvps.org/dmcritchie/excel/sorting.htm
 
Back
Top