Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I am using Excel 2003.
I have a spreadsheet where the data is organised with legal numbering and then some text. The problem is that the order when using the standard sort is (for example) 7.1, 7.10, 7.11, 7.12, 7.2, 7.3, 7.31, 7.32, 7.4, instead of 7.1, 7.2, 7.3, 7.4, 7.10, 7.11, 7.12, etc How do I get excel to recognise that 7.1 should be followed by 7.2 and not 7.10? The options button on the sort dialog box only offers Normal or months of the year. Thanks for any suggestions. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I usually have to use auxilarary columns which will contain 7 in one column
and 1 in next column Use these formula to get the integer and fractional part of the number. then copy the formula down the new columns. then sort on new columns =VALUE(LEFT(A1,FIND(".",A1)-1)) =VALUE(MID(A1,FIND(".",A1)+1,LEN(A1))) "Ladymuck" wrote: I am using Excel 2003. I have a spreadsheet where the data is organised with legal numbering and then some text. The problem is that the order when using the standard sort is (for example) 7.1, 7.10, 7.11, 7.12, 7.2, 7.3, 7.31, 7.32, 7.4, instead of 7.1, 7.2, 7.3, 7.4, 7.10, 7.11, 7.12, etc How do I get excel to recognise that 7.1 should be followed by 7.2 and not 7.10? The options button on the sort dialog box only offers Normal or months of the year. Thanks for any suggestions. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]() This is not easy! There is a user defined function (a macro) he'Sorting TCP/IP Addresses and the like' (http://www.mvps.org/dmcritchie/excel....htm#chaptsort) which has this function in:Function ChaptSort(cell As String) As String 'dmcritchie Dim i As Long, j As Long, n As Long Dim oldstr As String, newstr As String oldstr = cell i = 1 newstr = "" reloop: j = InStr(Mid(oldstr, i), ".") If j 5 Then ChaptSort = "#segment" Exit Function ElseIf j < 0 Then l = j - l newstr = newstr & "." & Left("0000", 5 - j) & Mid(oldstr, i, j - 1) i = i + j GoTo reloop Else If Len(oldstr) - i = 4 Then ChaptSort = "#length" Exit Function Else newstr = newstr & "." & Left("0000", 3 - (Len(oldstr) - i)) & Mid(oldstr, i) End If End If ChaptSort = "*" & Mid(newstr, 2) End FunctionThe formula in a worksheet would look like: =ChaptSort(B27) where B27 contains the legal numbering. It makes a sortable number (actually text). Add the macro (there's a link to *Getting Started with Macros and User Defined Functions* at the top of the website linked above) then in the worksheet add the formulae in a temporarily added column and sort on that column, then delete that column. That's it! If there are more than 4 numbers between full stops in your legal numbering (some call it Outline numbering) it'll fail, but can be amended if this is the case. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126308 |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Thanks to you both for your suggestions, it's a shame there isn't an easier
way but at least I know my sanity is (roughly) intact! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How? Sort Custom Numbering Scheme | Excel Discussion (Misc queries) | |||
Printing Legal | Excel Discussion (Misc queries) | |||
Is this legal? | Excel Worksheet Functions | |||
How can I keep my numbering system consecutive when I sort? | Excel Discussion (Misc queries) | |||
Sorting 'legal' numbers | Excel Discussion (Misc queries) |