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



My first post of this question can be found at:
http://www.microsoft.com/office/comm...e-06dca1ac3de3


---------------------------Rons
suggestion----------------------------------------------
Although I'm sure this can be done with worksheet functions, I found it
relatively simple to do using a VBA UDF (user defined function).

The "trick" is to transform the outline number appropriately, so that when
sorted it will give you the result you want. You then put that number in an
adjacent column to your table, and sort on that column. When done, you can
delete the column.

To enter the UDF, <alt<F11 opens the VB editor. Ensure your project is
highlighted in the project explorer window, then Insert/Module and paste the
code below into the window that opens.

Be sure to note the MaxNum constant which is the Maximum Number of outline
sublevels you might have. In your example, you showed four, so that is what I
used. In addition, I have assumed that the maximum number of levels within
each
sublevel is 99.

In a column adjacent to your table, enter the formula:
=combine(cell_ref) (where cell_ref is the outline number)
and copy/drag down as far as needed.

Then sort on this new column (ascending).

=================================
Function combine(rg As Range) As Double
Const dot As String = "."
Const NullString As String = ""
Const MaxLevels As Long = 4 'Maximum number of levels
Const MaxSubLevels = 99 'Maximum number of sublevels in each level; must be
10^x-1
Dim i As Long
Dim temp

temp = Split(rg.Text, dot)

For i = 0 To UBound(temp)
combine = combine + temp(i) * 10 ^ (((MaxLevels - 1) - i) _
* Log(MaxSubLevels + 1) / Log(10))
Next i

End Function
==================================

HTH,

--ron
---------------------End Rons
suggestion----------------------------------------------




Thanks aganin,
-Ian