Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Intern Ian
 
Posts: n/a
Default Sorting Numbers with Multiple Decimals

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.

For example

1 Office
1.1 Employees
1.1.1 Bob Marketing
1.1.2 Dave IT
€¦
1.1.12 Joe 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
€¦

Is there any way I can sort these alphabetically using the other two columns
and then get back to the proper numbering? A question that seems to be
similar or related to this can be found he
http://www.excelforum.com/showthread...hreadid=401416

Thanks,
-Ian

  #2   Report Post  
Dave Peterson
 
Posts: n/a
Default

So you want to get your data back into the original order?

If yes, insert a few columns to the right of the number column.

Then select that column
Data|Text to columns
delimited
by a dot
General for each field

Now you can sort your data by those helper columns---

Or you could insert one more column and use a formula like:
=text(d1,"000.")&text(e1,"000.")&text(f1,"000.")&t ext(g1,"000")
or maybe
=SUBSTITUTE(TRIM(IF(D1="","",TEXT(D1,"000 "))&IF(E1="","",TEXT(E1,"000 "))
&IF(F1="","",TEXT(F1,"000 "))&IF(G1="","",TEXT(G1,"000 ")))," ",".")

(Then the dots will look nice.)

Then your outlining numbers will look like:

001.001.012.001

If you convert this column of formulas to values (edit|copy, edit|paste
special|values), you'll be able to sort it nicely.



Intern Ian wrote:

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.

For example

1 Office
1.1 Employees
1.1.1 Bob Marketing
1.1.2 Dave IT
€¦
1.1.12 Joe 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
€¦

Is there any way I can sort these alphabetically using the other two columns
and then get back to the proper numbering? A question that seems to be
similar or related to this can be found he
http://www.excelforum.com/showthread...hreadid=401416

Thanks,
-Ian


--

Dave Peterson
  #3   Report Post  
Bernd Plumhoff
 
Posts: n/a
Default

Hi Ian,

Have a look at my spreadsheet sort_chapter.xls:
http://www.bplumhoff.de/software/sort_chapter.xls

HTH,
Bernd
  #4   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default

On Wed, 14 Sep 2005 16:22:02 -0700, "Intern Ian" <Intern
wrote:

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.

For example

1 Office
1.1 Employees
1.1.1 Bob Marketing
1.1.2 Dave IT
…
1.1.12 Joe 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
…

Is there any way I can sort these alphabetically using the other two columns
and then get back to the proper numbering? A question that seems to be
similar or related to this can be found he
http://www.excelforum.com/showthread...hreadid=401416

Thanks,
-Ian


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
  #5   Report Post  
Intern Ian
 
Posts: n/a
Default


Unfortunately I don't really understand how to use Excel's Visual Basic
function. I have entered the code you provided into a Module and saved it,
however I am still having trouble.

1) I dont think I understand what an outline number is. Could you give me
an example? I have been entering something like this: =combine(A2).

2) Regardless of what I put into the combine function, it says there is a
syntax error and highlights the first line of code.

I am working with Microsoft Office Excel 2003 (11.6355.6408) SP1 if that
makes any difference.

Also, I actually have up to 8 numbers separated by decimals, however I
expect I can change that by simply changing €œConst MaxLevels As Long = 4€ to
€ =8€ ?

Thank you very much for your help.


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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sorting formatted numbers Hunter Excel Discussion (Misc queries) 2 August 31st 05 02:09 AM
Sorting multiple columns together sweetledee Excel Discussion (Misc queries) 4 August 23rd 05 11:19 PM
Sorting numbers with brackets Itsy Excel Discussion (Misc queries) 4 August 8th 05 12:07 AM
rounding numbers, with-out decimals. IE:4507 to 4510 rollover22 Excel Worksheet Functions 3 July 27th 05 06:40 PM
How to convert numbers with apostrophe's (ex. 219'2) to decimals? Kaci Excel Worksheet Functions 2 June 15th 05 04:48 PM


All times are GMT +1. The time now is 10:56 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"