Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
If you use text to columns and choose General for each field, then the sort will
work as numbers. If you used text, then you'll have trouble. Another suggestion was to put the chapters in this kind of style to make it work with no intervention: 001.001.002.012 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. 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 -- Dave Peterson |
#3
![]() |
|||
|
|||
![]()
Ps. I copied Ron's code to a new module and I had to fix one comment line that
wrapped to an extra line: Const MaxSubLevels = 99 'Maximum number of sublevels in each level; must be '10^x-1 After that, it worked fine. 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. 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 -- Dave Peterson |
#4
![]() |
|||
|
|||
![]()
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 - |
#5
![]() |
|||
|
|||
![]()
Dave,
Thank you for your help. I have about 500 file numbers numbers and didn't want to have to retype all of them as three digit numbers (though I was close to doing it:-) But I do feel pretty foolish that I didn't recognise that 10^x-1 was just part of the coment line above. That was exactly my problem. This has taken care of everything thank you. -Ian "Dave Peterson" wrote: Ps. I copied Ron's code to a new module and I had to fix one comment line that wrapped to an extra line: Const MaxSubLevels = 99 'Maximum number of sublevels in each level; must be '10^x-1 After that, it worked fine. Dave Peterson |
#6
![]() |
|||
|
|||
![]()
Ron does nice work!
Intern Ian wrote: Dave, Thank you for your help. I have about 500 file numbers numbers and didn't want to have to retype all of them as three digit numbers (though I was close to doing it:-) But I do feel pretty foolish that I didn't recognise that 10^x-1 was just part of the coment line above. That was exactly my problem. This has taken care of everything thank you. -Ian "Dave Peterson" wrote: Ps. I copied Ron's code to a new module and I had to fix one comment line that wrapped to an extra line: Const MaxSubLevels = 99 'Maximum number of sublevels in each level; must be '10^x-1 After that, it worked fine. Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) | |||
Sorting Multiple Ranges | Excel Worksheet Functions | |||
Can you add multiple numbers in the same cell%3f | Excel Worksheet Functions | |||
Re What is the formula for adding multiple numbers in a cell | Excel Discussion (Misc queries) | |||
Counting multiple numbers in one cell | Excel Worksheet Functions |