Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Data, Text and Numeric
Hello,
I have a sheet of data I need sorted according to a value in a column (C), which is both text and numeric. You can see below that the C column doesn't sort the rows correctly as BG 10 should be after BC9 A B C 18 3:46 AR 7 19 4:24 AR 8 20 5:05 AR 9 21 2:49 BG 1 22 5:41 BG 10 23 3:08 BG 2 24 3:28 BG 3 The C column data always has two letters before the number (i can make it with or without the space before the number) I don't really want to have to use another column to store any values as it is already an overly complicated spreadsheet. Please could anyone help me with this? All reply's appreciated! Shorty |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Data, Text and Numeric
18 3:46 AR .7 19 4:24 AR .8 20 5:05 AR .9 21 2:49 BG .1 23 3:08 BG .2 24 3:28 BG .3 22 5:41 BG 10 All I can think of is using decimals to get the proper sor -- davesexce ----------------------------------------------------------------------- davesexcel's Profile: http://www.excelforum.com/member.php...fo&userid=3170 View this thread: http://www.excelforum.com/showthread.php?threadid=51790 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Data, Text and Numeric
Hi as fare as I now you have to use texttocolumns, here is an sample, I have recorded it and change som cod`s "I USE EXCEL 2003" Sub Makro2() Application.ScreenUpdating = False Columns("E:G").Insert Shift:=xlToRight Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(7, 1), Array(10, 1)), TrailingMinusNumbers:= _ True Rows("5:11").Sort Key1:=Range("E5"), Order1:=xlAscending, Key2:=Range("F5") _ , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _ False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _ :=xlSortNormal Columns("D:D").Insert Shift:=xlToRight Range("D5:D11").FormulaR1C1 = "=CONCATENATE(RC[1],"" "",RC[2],"" "",RC[3])" Range("D5:D11") = Range("D5:D11").Value Columns("E:H").Delete Shift:=xlToLeft Application.ScreenUpdating = True End Sub rwgards Yngve -- yngve ------------------------------------------------------------------------ yngve's Profile: http://www.excelforum.com/member.php...o&userid=31943 View this thread: http://www.excelforum.com/showthread...hreadid=517903 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Data, Text and Numeric
Hi,
Thanks for that, ive give it a quick go swapping the space with a decimal point '.' but i still cant get it to sort, am i missing something still? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Data, Text and Numeric
The easiest way ive found is just to add a '0' to the lower numbers,
ie: 86 4:57 NG08 87 5:17 NG09 88 5:33 NG10 89 1:01 NR01 90 1:37 NR02 91 2:12 NR03 though i dont really want the 0 in the data its the easiest way to do this. if anyone knows of a better work round or the real method to sort this keep the posts comming! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting Data, Text and Numeric
Since you asked "if anyone knows of a better work round"...
Using a helper column with the digits padded with zeros is the only way I know to do what you want. An alternative is my Special Sort Excel add-in that can sort by the last set of numbers. either... 18 3:46 AR 7 19 4:24 AR 8 20 5:05 AR 9 21 2:49 BG 1 23 3:08 BG 2 24 3:28 BG 3 22 5:41 BG 10 or... 21 2:49 BG 1 23 3:08 BG 2 24 3:28 BG 3 18 3:46 AR 7 19 4:24 AR 8 20 5:05 AR 9 22 5:41 BG 10 The drawback is that is a commercial application. You can read a review of it here... http://www.officeletter.com/blink/specialsort.html Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "Shorty" wrote in message... The easiest way ive found is just to add a '0' to the lower numbers, ie: 86 4:57 NG08 87 5:17 NG09 88 5:33 NG10 89 1:01 NR01 90 1:37 NR02 91 2:12 NR03 though i dont really want the 0 in the data its the easiest way to do this. if anyone knows of a better work round or the real method to sort this keep the posts comming! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Alphanumeric Sorting - numeric alpha numeric | Excel Worksheet Functions | |||
Sorting alpha numeric data | Excel Discussion (Misc queries) | |||
sorting and adding data that is not numeric | New Users to Excel | |||
Sorting more than 3 columns of Numeric data. | Excel Discussion (Misc queries) |