At work we use drafting software called Microstation. It originally ran on UNIX workstations, but was soon ported to PC’s. However there are still little UNIX touches. One weird thing in UNIX is how things are sorted. Usually when you sort things with numbers and letters in the name, the numbers sort to the top. In UNIX for whatever reason, the numbers sort to the bottom. Since Mac OS has been based on UNIX for a while (I guess this is why, I don’t know for sure), the iPod sorts songs the same way. So 10,000 Maniacs is at the end of my Artists list instead of the top.
So I’m in Microstation looking at a list of items and they are sorted that same funky way. And I’m trying to compare the hundreds of items in that list to a list of those items I have in an Excel spreadsheet. Some of those items have numbers in the name and that is messing me up. It is is a lot easier going down the list to make sure both are the same if they are in the same order. So I search the internet for a way to make Excel sort like UNIX with the numbers at the bottom. I can’t find anything. So I start looking for “numbers after letters” and stuff like that. I’m not finding anything. But I do come across “custom lists” which is a way to sort things other than alphabetically or numerically. The typical example is “January, February, March, . . . .” which you could make sort in that order instead of alphabetically (except you should just use date codes which sort normally). One of the example lists is a,b,c,d,e,f,…,v,w,x,y,z,0,1,2,3,4,5,6,7,8,9 so it looks like I’m getting somewhere. I try sorting my list that way. This doesn’t work at all because all of the items in my list have at least 3 character (no more than 6) and the first character is always a letter. So it sorts exactly the same way and ignores the 2nd through 6th characters. The custom list only sorts on the first letter! This is very aggravating. I thought well maybe I could make a huge list that goes a1, a2, a3, a4,…c7,c8,c9,d0, etc. But how many places would I need? That could get daunting. Plus Excel limits the list to 255 items.
Eventually I got it working by creating 6 new columns of data and putting the first character in the first column, the second character in the next, and so on using a MID function. Then I did a custom sort using that list on the first column, then the second, then the third, etc. This kind of worked except on items with short names when I wound up with blank spaces and for some reason the blanks sorted to the bottom instead of the top. Who does that?! Blanks should always sort to the top. So now I have to put in an IF statement in every column and say if there is no letter in that place, make the value # instead of just empty. Then I put # at the top of the custom list so it would sort to the top. Highlight this huge bunch of data, and sort all 6 columns and done.
Almost done. One of the “features” of Excel (some call them bugs, Microsoft calls them features) is it can “Sort anything that looks like a number as a number” or “Sort numbers and numbers stored as text separately”. I’m not even sure what that means with respect to what I’m doing, but the default result was L came after R (or something; it wasn’t working). I think it was thinking that the lowercase L was a 1. Fortunately after choosing the other option, everything sorted just like my UNIX list and I was able to finish checking.