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
> > >