sort order
 

I have an excel spreadsheet with approx 60k records. Try to sort these in
order


Example:
20A100-6 - 11A
20A102-8L/R - 11A
20A103-4 - 11A
20A103-5 - 11A
20A10-4 - 11A
20A104-204 - 11A
20A104-207 - 11A
20A104-263 - 11A
20A11-23 - 11A
20A113-34 - 11A
20A113-50 - 11A
20A113-66 - DLT
20A11-44 - 11A
20A114-4 - 11A RPL#1
20A11-46 - 11A
20A11-53 - DLT
20A11-55 - DLT/CT
20A11-56 - DLT/CT
20A11-61 - DLT
20A120-9 - 11A

Assuming the first three characters will be alpha numeric
first make three helper columns (B,C,D)
=left(A1,find("-",A1)-1)
=Trim(MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1))
=trim(RIGHT(A1,LEN(A1)-FIND("-",A1,FIND("-",A1)+1)))
If you want 20A11 to sort before 20A104
add helper column (E)
=if len(B1)=6,B1,Left(B1,3)&if(len B1=5,"0"&right(B1,2),"00")&right(B1,1)))
if you want 20A11 to sort after 20A104
add helper column (E)
=B1&if(len(B1)=5,"0",if len(B1)=4,"00",""))
on the middle series
It depends on what the variation would be for a common first series and
whether you would want a -9 to sort before or after a -61 and whether the
Alpha numeric sort by the numberic first or should be classified at text
The same informaiton is nneded for the last section.
you might have to set up some helper columns for those sections but if you
don't, select all and sort by column E first, column C second and Column D
last,
If you had to have helper columns to sort within the middle and End sections
sort by those in the instead of Columns C and D as appropriate.

"DGNVSPORTS" wrote:

I have an excel spreadsheet with approx 60k records. Try to sort these in
order


Example:
20A100-6 - 11A
20A102-8L/R - 11A
20A103-4 - 11A
20A103-5 - 11A
20A10-4 - 11A
20A104-204 - 11A
20A104-207 - 11A
20A104-263 - 11A
20A11-23 - 11A
20A113-34 - 11A
20A113-50 - 11A
20A113-66 - DLT
20A11-44 - 11A
20A114-4 - 11A RPL#1
20A11-46 - 11A
20A11-53 - DLT
20A11-55 - DLT/CT
20A11-56 - DLT/CT
20A11-61 - DLT
20A120-9 - 11A

BJ,i dont understand what you are saying.
I need to be able to sort by sequence (1 before 2, A before C) throughout
the whole Number/Alpha set. I have never done anything like this before and
i don't understand what you are telling me below. Where do i go in excel to
do these helper columns .

Thanx

"bj" wrote:

Assuming the first three characters will be alpha numeric
first make three helper columns (B,C,D)
=left(A1,find("-",A1)-1)
=Trim(MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1))
=trim(RIGHT(A1,LEN(A1)-FIND("-",A1,FIND("-",A1)+1)))
If you want 20A11 to sort before 20A104
add helper column (E)
=if len(B1)=6,B1,Left(B1,3)&if(len B1=5,"0"&right(B1,2),"00")&right(B1,1)))
if you want 20A11 to sort after 20A104
add helper column (E)
=B1&if(len(B1)=5,"0",if len(B1)=4,"00",""))
on the middle series
It depends on what the variation would be for a common first series and
whether you would want a -9 to sort before or after a -61 and whether the
Alpha numeric sort by the numberic first or should be classified at text
The same informaiton is nneded for the last section.
you might have to set up some helper columns for those sections but if you
don't, select all and sort by column E first, column C second and Column D
last,
If you had to have helper columns to sort within the middle and End sections
sort by those in the instead of Columns C and D as appropriate.

"DGNVSPORTS" wrote:

I have an excel spreadsheet with approx 60k records. Try to sort these in
order


Example:
9th 20A100-6 - 11A
10th 20A102-8L/R - 11A
11th 20A103-4 - 11A
12th 20A103-5 - 11A
1st 20A10-4 - 11A
13th 20A104-204 - 11A
14th 20A104-207 - 11A
15th 20A104-263 - 11A
2nd 20A11-23 - 11A
16th 20A113-34 - 11A
17th 20A113-50 - 11A
18th 20A113-66 - DLT
3rd 20A11-44 - 11A
19th 20A114-4 - 11A RPL#1
4th 20A11-46 - 11A
5th 20A11-53 - DLT
6th 20A11-55 - DLT/CT
7th 20A11-56 - DLT/CT
8th 20A11-61 - DLT
20th 20A120-9 - 11A

A helper column is just a column which has nothing in it so that when you add
equations you are not writing over important information.
I assumed you wanted to sort first by sections as indicated by the dash so
i separated the information into three sections since there were two dashes
The other portions of my response were ways to define hoe I assumed you
wanted things sorted.
for example if you have a "2" and an "11" at the end of an otherwise
identical statement which comes first in an alphanumeric series the "11"
would come first. in A numeric series the 2 would come first. Neither is
logically right or wrong. It depends on how you need the data.



"DGNVSPORTS" wrote:

BJ,i dont understand what you are saying.
I need to be able to sort by sequence (1 before 2, A before C) throughout
the whole Number/Alpha set. I have never done anything like this before and
i don't understand what you are telling me below. Where do i go in excel to
do these helper columns .

Thanx

"bj" wrote:

Assuming the first three characters will be alpha numeric
first make three helper columns (B,C,D)
=left(A1,find("-",A1)-1)
=Trim(MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-FIND("-",A1)-1))
=trim(RIGHT(A1,LEN(A1)-FIND("-",A1,FIND("-",A1)+1)))
If you want 20A11 to sort before 20A104
add helper column (E)
=if len(B1)=6,B1,Left(B1,3)&if(len B1=5,"0"&right(B1,2),"00")&right(B1,1)))
if you want 20A11 to sort after 20A104
add helper column (E)
=B1&if(len(B1)=5,"0",if len(B1)=4,"00",""))
on the middle series
It depends on what the variation would be for a common first series and
whether you would want a -9 to sort before or after a -61 and whether the
Alpha numeric sort by the numberic first or should be classified at text
The same informaiton is nneded for the last section.
you might have to set up some helper columns for those sections but if you
don't, select all and sort by column E first, column C second and Column D
last,
If you had to have helper columns to sort within the middle and End sections
sort by those in the instead of Columns C and D as appropriate.

"DGNVSPORTS" wrote:

I have an excel spreadsheet with approx 60k records. Try to sort these in
order


Example:
9th 20A100-6 - 11A
10th 20A102-8L/R - 11A
11th 20A103-4 - 11A
12th 20A103-5 - 11A
1st 20A10-4 - 11A
13th 20A104-204 - 11A
14th 20A104-207 - 11A
15th 20A104-263 - 11A
2nd 20A11-23 - 11A
16th 20A113-34 - 11A
17th 20A113-50 - 11A
18th 20A113-66 - DLT
3rd 20A11-44 - 11A
19th 20A114-4 - 11A RPL#1
4th 20A11-46 - 11A
5th 20A11-53 - DLT
6th 20A11-55 - DLT/CT
7th 20A11-56 - DLT/CT
8th 20A11-61 - DLT
20th 20A120-9 - 11A