Sorting Numbers (n.n.n) (Excel 2000)

Topic

New Reply

Hello

I’m interested in discovering if there is an easier way of doing this than how I’ve done it to date. We have a number of requirements (a lot actually!) which have numeric identifiers in the format “4A.09.99.99” which are in Excel and also in Word. The numbering schema is from Word and we are stuck with this and cannot change it. The issue is that the third and fourth number pairs contain numbers that do not have leading zeros (i.e. 1, 2, 3,…11, 12, 13,…etc). Consequently when these fields are sorted in Excel we have 4A.01.11.1 sorted before 4A.01.2.1 and so on across the board.

I have managed to reach a workaround by using Excel formulas to insert the leading zeros in the last two fields so that 4A.01.2.1 becomes 4A.01.02.01 and these will then sort properly in ascending sequence but I have issues in these varying from the original and don’t really want to keep swapping back and forth. I realise that I could also create multiple cells containing each number group and sort hierarchically across multiple columns but before I continue with either process I am interested in discovering if there is any other solution that leaves the original data untouched.

Has anyone solved this problem in an “elegant” manner without resorting to one of my workarounds?

Thanks

Reply | Quote

Replies

Say that the original ‘numbers’ as in Word are in column A. You can use formulas in column B to obtain sortable ‘numbers’. You can keep column A, yet sort on column B. You can even hide column B after sorting.

Reply | Quote

Thanks Hans

I know that I can do that which is how I’ve resolved it so far. I was hoping there might be some way to convince Excel to sort it correctly without resorting to that but I expect it can’t be done.

Thanks

Reply | Quote

Perhaps in a future version. At the moment, you’ll have to resort to an extra column for sorting purposes.

Reply | Quote

In my opinion, excel is sorting it “correctly”.
1) I don’t think you will ever get a program to work directly on this. You will always have to create a “temporary column” to put it into a “perceived “correctness”
2) You are sorting text, not a number which is sorted on the priority from left to right regardlesss of length of the string. Numbers are dependent on their length for the value.
3) Even if a portion of this were real “numbers”
01.11 is still less than 01.2

Just my
Steve

Reply | Quote

Hi Steve

I guess I have to agree with you on that.

Oh well. I figured it wouldn’t hurt to ask.

Thanks guys!

Reply | Quote

The Open Office Calc Specifications for version 2.0 state:[indent]

---

A new sort algorithm known as the “natural sort” will be added to the cell sort options to provide users an option of sorting string-prefixed numbers in the “natural” way i.e. A1, A2, A3, … , A19, A20 instead of the current behavior of A1, A10, A11, A12, … ,A19, A2, A20, A3, A4, … , A8, A9.

---

[/indent]So who knows…

Reply | Quote

Perhaps, but that is “simpler” than this. I assume the open office parses into text and numbers and works on those 2 segments. This involves parsing it into several number strings (and complicates it since the period is a delimiter, not a decimal).

I don’t imagine you will find too many people working on algorithms to sort: 1.11 before 1.2 as is requested in the above…

Steve

Reply | Quote

John,
You may want to try my Excel add-in “Special Sort”.
It should do what you want as it will sort “decimal” numbers into strict numerical order.
The add-in has a total of 12 different sort methods available including color, prefix/suffix, and date.
It will also sort cell borders (optional) with the data.
In my tests it did 1000 rows with decimals in about 6/10 second.
It looks and reacts similar to the existing Excel sort utility.
Comes with a Word.doc install/use file.
Available -free- upon email request. Remove XXX from my address.

Regards,
Jim Cone
San Francisco, USA
jim.coneXXX@rcn.comXXX

Reply | Quote

Thanks Jim

You should have “mail”.

Reply | Quote

John,
No mail from Australia has been received here in San Francisco.
Currently 8:55 pm Saturday night.
(remove XXX from email address)
Jim Cone
jim.coneXXX@rcn.comXXX

Reply | Quote

Hi Jim

I’m not sure why that email hasn’t reached you yet. It is currently 10:25 pm San Francisco time as I write this and my email to your [correct] email address left here 3 hours ago. Maybe it is just hitching a slow boat across the Pacific and will arrive later. I’m patient.

John

Reply | Quote

Hi Jim

In case my email still hasn’t reached you.

You can reach me through john at heidemann dot id dot au

Thanks!

Reply | Quote

Hello Jim

Thankyou for that add-in. It sorts the data exactly the way that I needed.

I haven’t explored the other sort options that it provides but they all look extremely practical and useful too.

Many thanks!

Reply | Quote