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. 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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
Hi Ian,
Have a look at my spreadsheet sort_chapter.xls: http://www.bplumhoff.de/software/sort_chapter.xls HTH, Bernd |
#5
![]() |
|||
|
|||
![]() 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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Sorting formatted numbers | Excel Discussion (Misc queries) | |||
Sorting multiple columns together | Excel Discussion (Misc queries) | |||
Sorting numbers with brackets | Excel Discussion (Misc queries) | |||
rounding numbers, with-out decimals. IE:4507 to 4510 | Excel Worksheet Functions | |||
How to convert numbers with apostrophe's (ex. 219'2) to decimals? | Excel Worksheet Functions |