II,
Saw your previous post and that prompted me to add a
"decimal" sort to my Special Sort Excel add-in.
I just used it to sort the example you posted and came up with...
3.4.1
3.4.2
3.4.3
3.4.10
3.4.11
It uses a technique similar to what Dave Peterson posted in that
it pads all the numbers betweens dots with zeros, so all numbers
are the same length. This of course is done under the covers and
the user just sees the data change to the new sorted order.
(on my machine, it did 1000 rows in about 6/10 of a second)
The code that Bernd Plumhoff posted worked for me and I was very
impressed with it, but decided to go a different direction with my
add-in code.
The add-in provides a total of 12 different methods to sort with,
including by color, numbers only, dates, reverse, length, prefix, suffix
middle (you pick the starting position and length) and even a random sort.
It looks and responds somewhat like the built in Excel utility.
You can be the first to try out the decimal sort. (release 1.32)
It comes with a two page Word.doc install/use file.
The add-in is - free - upon direct request.
Remove XXX from my email address.
Jim Cone
San Francisco, USA
XX
"Intern Ian"
wrote in message
...
I am attempting to create a file system using excel that can be easily
searched and sorted by a number ie 1.3.12 or alphabetically. At this time I
have entered the data as I would like it to appear if sorted by number,
however if I attempt to alphabetize I can not restore the numbers to their
proper order.
Currently I have only 3 columns. One with numbers, one with file names, and
one with additional information. With the numbering extending to 8 numbers
(15.1.1.3.11.4.7.1)
Here is a more detailed example of what I have.
1 Office
1.1 Employees
1.1.1 Bob Marketing
1.1.2 Jan IT
€¦
1.1.12 Chris Marketing
1.2 Equipment
1.2.1 Computers
€¦.
12 Locations
12.1 USA
12.1.1 New York
12.1.1.1 New York Primary
12.2 New Mexico
12.2.1 Albuquerque Primary
€¦
12.2.15 California
12.2.15.1 L.A. Secondary
€¦
I recently posted this same question, however could not seem to get the
suggested macros to work. I have included the suggestions I received below
with a description of why they did not work for me.
Dave Peterson suggested putting each number in a separate column and sorting
that way. This amounts to almost the same thing as Bernd Plumhoff suggested,
however he did it by creating an example macro that can be downloaded from
this link: http://www.bplumhoff.de/software/sort_chapter.xls
In both cases, however, the numbers still do not end up on the correct
order. The numbering ends up going something like:
3.4.1
3.4.10
3.4.11
3.4.2
3.4.3
Ron Rosenfeld made a suggestion which I have included below. When I
attempted to run the included code it said there is a syntax error in the
first line€¦and highlighted it yellow. Also, to work the new function I have
to type =combine(A1) (or whatever cell my number with multiple decimals is
in)?
Any more help would be much appreciated.
-Ian
- snip -