Saturday, February 27, 2010

Sort a list of addresses in a walking order

Suppose you need to walk in a street using a list showing the odd numbers ascending first and then the even ones descending, a kind of a postman's walk in a four lanes street.

One way to solve the problem is to reorder the odds or the evens by making them negatives. This requires an extra effort because Excel doesn't like that when sorting lists. A better approach is to subtruct the street numbers from a large enough constant. Let's say that our constant would be the sum of all the street numbers. Applying the formula and concatenating it with the street name will solve the problem.

Here is the original list in columns A:Bwith the formula applied in the extra column C: