CUSTOM SORT
Posted by YANECKC on January 19, 2001 11:56 AM
I'M TRYING TO SET UP A CUSTOM SORT FOR ID NUMBER.
THE FIELD IS 7 BYTES LONG. HERE IS AN EXAMPLES OF HOW
I WANT THE COLUMN TO LOOK AFTER SORT.
A000027
A030932
A189851
C214716
D001043
D553779
F393700
G136114
G533006
0567047
0567223
0567285
1C00602
5C10201
5C39029
5C61272
5C63675
5P76771
5000341
5801755
6A00215
9Q81859
9T24029
9318126
Posted by Mark W. on January 19, 2001 12:10 PM
Please describe how you think this sort should
work.
Posted by YANECKC on January 19, 2001 12:43 PM
yaneck
RIGHT NOW EXCEL SORT ORDER IS 0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ.
CAN I SORT MY 7 BYTE FIELD BY ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789
THE EXAMPLES SHOW SEVERAL ID NUMBER. THE PROBLEM
IS IT IS MADE UP OF NUMBERS AND LETTERS.
Posted by Mark W. on January 19, 2001 1:10 PM
What if you calculated a checksum and used it for
sorting purposes? If this interests you try:
{=SUM((CODE(MID(A1,{1,2,3,4,5,6,7},1))+ISNUMBER(MID(A1,{1,2,3,4,5,6,7},1)+0)*43)^{6,5,4,3,2,1,0})}
Posted by YANECKC on January 19, 2001 1:23 PM
YANECK
THAT COLUMN IS A TEXT COLUMN AND IS ONLY USED FOR SORT .
THE ONLY PROBLEM IS THAT IN EACH CELL IT HAS NUMBERS AND LETTERS.
I JUST NEED TO SORT THAT COLUMN AND THE ORDER
HAS TO BE LETTERS FIRST THEN NUMBERS.
Posted by Mark W. on January 19, 2001 1:28 PM
Yaneck, what I was suggesting was:
1. Insert a new column B.
2. Enter {=SUM((CODE(MID(A1,{1,2,3,4,5,6,7},1))+ISNUMBER(MID(A1,{1,2,3,4,5,6,7},1)+0)*43)^{6,5,4,3,2,1,0})}
into cell B1.
3. Copy the formula in B1 down to cell B24.
4. Sort on column B.
Posted by YANECKC on January 19, 2001 2:26 PM
MARK
THANK YOU!
THE FORMULA WORKS GREAT!
THANKS AGAIN!
