| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sorting help needed!
I have a column of data in random order and when I Sort the data by ascending order (I have hundreds of pages of these), the result is as below: 62062 62063 62093 89120 20294 SAT 20294 WH 20309 WH 20310 BLK 20310 WH 23109 COLBALT 25013 OS 50362 BS 50362 SB 50367 BS 50368 BS 50370 BS 50370 SB 50398 BS 52030 BS 52032 BS 52106 BS FR 52133 BS 52203 BS 63204 MC/FR 87000 BLK 901 RT BS 930V/C B I need for the data to be in numerical order (ascending) so that (for example) the 901 RT BS is at the top of the list b/c 901 is a smaller value then 930 and the 89120 would be the last b/c 89120 is the largest value number, etc... and I need that to occur even if ther is text at the end of the number. How do you tell Excel to do this? Thanks, Brett -- BrettOlbrys ------------------------------------------------------------------------ BrettOlbrys's Profile: http://www.excelforum.com/member.php...fo&userid=1786 View this thread: http://www.excelforum.com/showthread...hreadid=496583 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting help needed!
Probably not the answer you want - but copy your sample to a Word Document and it will sort as you want. Sort Order in Word 901 RT BS 930V/C B 20294 SAT 20294 WH 20309 WH 20310 BLK 20310 WH 23109 COLBALT 25013 OS 50362 BS 50362 SB 50367 BS 50368 BS 50370 BS 50370 SB 50398 BS 52030 BS 52032 BS 52106 BS FR 52133 BS 52203 BS 62062 62063 62093 63204 MC/FR 87000 BLK 89120 -- wjohnson ------------------------------------------------------------------------ wjohnson's Profile: http://www.excelforum.com/member.php...o&userid=29640 View this thread: http://www.excelforum.com/showthread...hreadid=496583 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting help needed!
Are these imported data? I experienced similar problems with imported data,
e.g. converted from a .TXT file. If so, you should first transform them (e.g. in a helper column) with the TEXT(cellreference,"@") function then sort by the transformed column (you can copy and pastespecial-values the transformed column back to their original place, if necessary). Regards, Stefi €˛BrettOlbrys€¯ ezt Ć*rta: I have a column of data in random order and when I Sort the data by ascending order (I have hundreds of pages of these), the result is as below: 62062 62063 62093 89120 20294 SAT 20294 WH 20309 WH 20310 BLK 20310 WH 23109 COLBALT 25013 OS 50362 BS 50362 SB 50367 BS 50368 BS 50370 BS 50370 SB 50398 BS 52030 BS 52032 BS 52106 BS FR 52133 BS 52203 BS 63204 MC/FR 87000 BLK 901 RT BS 930V/C B I need for the data to be in numerical order (ascending) so that (for example) the 901 RT BS is at the top of the list b/c 901 is a smaller value then 930 and the 89120 would be the last b/c 89120 is the largest value number, etc... and I need that to occur even if ther is text at the end of the number. How do you tell Excel to do this? Thanks, Brett -- BrettOlbrys ------------------------------------------------------------------------ BrettOlbrys's Profile: http://www.excelforum.com/member.php...fo&userid=1786 View this thread: http://www.excelforum.com/showthread...hreadid=496583 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting help needed!
It's not feasable to sort in Word b/c I have 200+ pages like this to sort. The data was not imported, it was typed directly into the cells. I simply want the data to ascend from lowest to highest just like the Word example above, so how do you do in Excel when there is text in there too? Thanks, Brett -- BrettOlbrys ------------------------------------------------------------------------ BrettOlbrys's Profile: http://www.excelforum.com/member.php...fo&userid=1786 View this thread: http://www.excelforum.com/showthread...hreadid=496583 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting help needed!
Assume the values start in A1, in B1 put
=IF(ISERR(--A1),--LEFT(A1,MATCH(FALSE,ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)-1),A1) entered with ctrl + shift & enter (if column B or the adjacent column is not available insert a new column to the right and put the formula there adapted to your real values) copy down as long as needed then select both columns and sort by the help column -- Regards, Peo Sjoblom (No private emails please) "BrettOlbrys" wrote in message ... It's not feasable to sort in Word b/c I have 200+ pages like this to sort. The data was not imported, it was typed directly into the cells. I simply want the data to ascend from lowest to highest just like the Word example above, so how do you do in Excel when there is text in there too? Thanks, Brett -- BrettOlbrys ------------------------------------------------------------------------ BrettOlbrys's Profile: http://www.excelforum.com/member.php...fo&userid=1786 View this thread: http://www.excelforum.com/showthread...hreadid=496583 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting help needed!
On Wed, 28 Dec 2005 22:46:19 -0600, BrettOlbrys
wrote: I have a column of data in random order and when I Sort the data by ascending order (I have hundreds of pages of these), the result is as below: 62062 62063 62093 89120 20294 SAT 20294 WH 20309 WH 20310 BLK 20310 WH 23109 COLBALT 25013 OS 50362 BS 50362 SB 50367 BS 50368 BS 50370 BS 50370 SB 50398 BS 52030 BS 52032 BS 52106 BS FR 52133 BS 52203 BS 63204 MC/FR 87000 BLK 901 RT BS 930V/C B I need for the data to be in numerical order (ascending) so that (for example) the 901 RT BS is at the top of the list b/c 901 is a smaller value then 930 and the 89120 would be the last b/c 89120 is the largest value number, etc... and I need that to occur even if ther is text at the end of the number. How do you tell Excel to do this? Thanks, Brett Use a helper column which contains only the numeric portion, and sort on that. For example: With your data in A1:An B1: =--LEFT(A1,MATCH(TRUE,ISERROR(-MID(A1,ROW(INDIRECT("1:255")),1)),0)-1) This is an **array** formula, so after typing or pasting it into the formula bar, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. Select B1 and copy/drag the formula down to Bn. This will extract the numeric portion of the values in column A. Select An:Bn and, from the main menu: Data/Sort Column B Ascending. --ron |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting help needed!
Works great. Thank you! Brett -- BrettOlbrys ------------------------------------------------------------------------ BrettOlbrys's Profile: http://www.excelforum.com/member.php...fo&userid=1786 View this thread: http://www.excelforum.com/showthread...hreadid=496583 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Sorting help needed!
On Thu, 29 Dec 2005 23:58:43 -0600, BrettOlbrys
wrote: Works great. Thank you! Brett You're welcome. Glad to help. --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Maintaining cell reference after sorting | Excel Discussion (Misc queries) | |||
| sorting non contiguous ranges | Excel Discussion (Misc queries) | |||
| sorting number in ascending order | Excel Discussion (Misc queries) | |||
| SORTING question | New Users to Excel | |||
| Adding a KeyID column for sorting | New Users to Excel | |||
Linear Mode
