Sort by truncated year in serial number

**MPICrusher** · 03-11-2011, 01:36 PM

I am trying to sort a column within a table that contains a five or six digit serial number. First two digits are the year (77 through 11, for 1977 through 2011) and last three or four digits are the actual serial number. Unfortunately, since the dates don't include the 19 or 20 prefix the serial numbers don't sort in true "chronological" order, just as hard numbers. I can't add the 19 or 20 prefixes to the serial number (unless they won't be visible to the user when displayed). File resides on unix mainframe on network and therefore any macros or VBA may be problematic. Please see attached file for numbers in question.

Thanks for any help!

**6StringJazzer** · 03-11-2011, 11:05 PM

Welcome to the Forum!

You thought you were asking a simple question, didn't you?

How are users viewing an Excel file on a Unix mainframe? The answer may affect what solution will work. If they can't run macros then there may be other limitations as well.

Who is going to sort--you before you post the file, or the users after they retrieve it?

How do want to sort--as a numeric sort, or textual sort? That is, if you have

199801
1997001

Which one do you want to come first? In a numeric sort, you end up in the order I showed. In a textual sort, it would be the reverse. Because your numbers are not all the same length, it matters.

When you read my suggestion you will see why I am asking all this. One option is to add a second column with this formula:

=IF(LEFT(A1,2)>="77",19,20)&A1

and then sort on that column. If you want you can delete the column after sorting. Alternatively you can make the font in this column white so the user won't see it, unless they highlight any cells in that column.

One problem with that solution is that your data is numeric but you have formatted display to show leading zeroes but only in some cases. It's hard to figure out what you did. So if you see

000954

the result of grabbing the first two digits is not 00, it's 95. Therefore the formula doesn't work on those. In fact, nothing works because the special formats you are using are not detectable using Excel functions, so there is no for formulas to tell the difference between these values:

954
0954
00954

Sorry to respond to your question with so many questions.

**DonkeyOte** · 03-12-2011, 03:59 AM

This is just an extension of the points made by 6SJ above...

Originally Posted by MPICrusher

last three or four digits are the actual serial number

I suspect based on the data that prior to 2000 the codes were 3 digits and thereafter 4, correct ?

However, even if that is indeed the case you still can't distinguish between:

85104

085104

Given the latter's leading zero is the result of a custom format rather than a literal character (a point already made by 6SJ)

These two issues combined precludes you from establishing the year via the two leftmost characters of the underlying value
with the above values both would return 85 and we'd thus assume 3 digit code

So in short - as is - I don't think you can do what you want without use of some code - can you run XLM for ex. if you save as .xls ?

With B1 the active cell you could create the following name:

Please Login or Register  to view this content.

Then

Please Login or Register  to view this content.

but again this may not even be feasible for you and based on OP you may not even want to use an additional column

(using the above in 2007 format would necessitate .xlsm extension)

**MPICrusher** · 03-15-2011, 10:36 AM

Sorry I missed replying promptly, I was out this weekend. To answer some ?s:

-This .xls file resides on a "device" that runs UNIX. That much I know, if there is a difference between a server and a mainframe and some other options I'd have to find out.

-Users would sort if desired after opening file.

-Leading zeroes indicate in all cases machines made in year 2000.

-Three digit to four digit serial number "suffix" change occured in 2000.

-The reason I mentioned that macros might not work is because I tried some for a different issue previously and it created a little bit of a problem (multiple versions of Excel between users, network permissions, etc etc.) Boss was rather unhappy I had played with that without getting an OK first. Oops.

-I see what you're both saying in the two samples about not being able to distinguish between a 3 or 4 digit serial number and truncating the year. At first I thought it was moot because all 3 digit #s come before 4 digit #s and change occured in one year. That comes from visualizing it as a year. I mentally put the 19 or 20 (whichever is req'd) in front of the number, which I was trying to get Excel to do without displaying it (as the real serial number unfortunately doesn't have these leading digits).

I hope these additional points help clarify things a bit. Sorry again for taking so long to get back to this post, hopefully some folks still see it!