Sorting numbers

[billfan]

I'm trying to sort a list of codes I have in one column in excel, about 100 rows worth. The problem that I'm having is that I want to sort them in overall increasing chronological order, but Excel keeps putting the codes with letters at the end of them in their own order at the end, after all the "number only" codes are done being sorted. They look like this

09879
11334
14589
34875
36527
54091
54283
75893
99211
23143T
36345F
38456F
59840B
59840D

But I want them to sort like this:
14589
23143T
34875
36345F
36527
38456F
54091
54283
59840B
59840D
75893
99211

I'm using AutoFilter, then the Data-->Sort function, but it keeps sorting them the first way. I need it the second way. Any thoughts? Thanks in advance!

 

[hatman]

Check out this post of mine... it only works for the first 8 characters of a text string, which was less than ideal for me... and is NOT case sensitive (mainly because this needs to use 2 digit Ascii codes, and the lower case letters extend beyond 100) but I think it'll work for you.

http://www.mrexcel.com/board2/viewtopic.php?t=234858

 

[jindon]

Hi
What do you want to do with

09879
11334

They are missing in your result....Should be excluded?

 

[hatman]

Here is my ranking tool modified for your application... I had to force all strings to be the same length. Note that your sorted list from above is in column AQ for comparison... minus the discrepency that jindo pointed out, this seems to work.

Book1
	AJ	AK	AL	AM	AN	AO	AP	AQ
1	6		9879	57565557323	13	11334
2			11334	49495151523	1	14589		14589
3			14589	49525356573	2	23143T		23143T
4			34875	51525655533	4	34875		34875
5			36527	51545350553	6	36345F		36345F
6			54091	53524857493	8	36527		36527
7			54283	53525056513	9	38456F		38456F
8			75893	55535657513	12	54091		54091
9			99211	57575049493	14	54283		54283
10			23143T	50514952518	3	59840B		59840B
11			36345F	51545152537	5	59840D		59840D
12			38456F	51565253547	7	75893		75893
13			59840B	53575652487	10	9879
14			59840D	53575652487	11	99211		99211
Sheet1

 

[erik.van.geit]

Hi,

perhaps use a helpercolumn
="&" & A1
then sort both columns together and use other column as key

   A       B        
 1 09879   &09879   
 2 11334   &11334   
 3 14589   &14589   
 4 23143T  &23143T  
 5 34875   &34875   
 6 36345F  &36345F  
 7 36527   &36527   
 8 38456F  &38456F  
 9 54091   &54091   
10 54283   &54283   
11 59840B  &59840B  
12 59840D  &59840D  
13 75893   &75893   
14 99211   &99211   

Blad1
[Table-It] version 06 by Erik Van Geit

RANGE   FORMULA (1st cell)
B1:B14  ="&" & A1

[Table-It] version 06 by Erik Van Geit

kind regards,
Erik

 

[Peter_SSs]

billfan

I am interested in the answer to jindon's question but assuming you accidentally left them out of your solution, this may be of use. It is based on all the 'number' parts of your codes being 5 characters long as in the samples provided.

1. In B1 (copied down): =LEFT(A1,5)
2. Now sort columns A and B using column B as the sort key

This is my result after the sort:

Mr Excel.xls
	A	B	C	D
1	09879	09879
2	11334	11334
3	14589	14589
4	23143T	23143
5	34875	34875
6	36345F	36345
7	36527	36527
8	38456F	38456
9	54091	54091
10	54283	54283
11	59840B	59840
12	59840D	59840
13	75893	75893
14	99211	99211
15
Sort

 

[hatman]

perhaps use a helpercolumn
="&" & A1
then sort both columns together and use other column as key

Good point Erik... it took me a second to realize what you were doing differently from my first attempt, which didn't work... that was because I was trying to turn these strings into text using the Text() function and/or leading/trailing spaces... but it was too late last night for me to realize that those methods were too subtle to prevent Excel from looking at number-like strings as numerals.

Someday I may be able to find simple solutions :wink: Until then, I'll just keep making things more complicate dthan they have to be

 

[Peter_SSs]

perhaps use a helpercolumn
="&" & A1

Erik, is there a reason for
="&" & A1
rather than, say, just
="" & A1

 

[hatman]

Yup... try it. If you simply concatenate a space or a logical blank to turn the numbers into text, Excel STILL insists on interpretting those number-like strings as numerals, and you haven't made any gains... by concatenating some text character (could be anything, as long as it is the same for everything) it defeats Excel's insistance on second-guessing that it's a numeral because it looks like one, even though it's reallly text.

Edit: I did the same double take... which prompted my previous post

 

[erik.van.geit]

hatman,

you would be a good lawyer
thanks