Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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 |
#2
![]() |
|||
|
|||
![]()
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 |
#3
![]() |
|||
|
|||
![]()
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 |
#4
![]() |
|||
|
|||
![]()
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
How can I sort multiple months/years WITHOUT Alpha order taking o. | Excel Worksheet Functions | |||
I have two identical pivot tables with different sort order of th. | Excel Discussion (Misc queries) | |||
how do I reset the default sort order in excel xp back to blanks . | Excel Discussion (Misc queries) | |||
how do i sort excel worksheets by alphabetical order? | Excel Worksheet Functions | |||
Need to sort dates before 1900 in proper order | Excel Discussion (Misc queries) |