# Custom Sorting

In the above (obviously contrived) example, a company changed accounting systems. Any open projects at the time were converted to the new system, but were assigned new project numbers and names. Now the company needs to sort the reports by project number, but they want those jobs that have two different numbers to remain together.

To accomplish this, we need a new column (that we will be sorting on) and a lookup table showing both the old and new project numbers.

The new Sort Project column uses this formula:

=IF(ISNA(MATCH(A2,\$E\$2:\$E\$3,FALSE)),A2,VLOOKUP(A2,\$E\$2:\$F\$3,2,FALSE))

That says: if you can’t find this project in column E, give me column A, otherwise give me column F. Now I can sort on column C and get Project One and New Project One sorted together.

Posted in Uncategorized

## 3 thoughts on “Custom Sorting”

1. Michele says:

Everyone was impressed. I tell them your excel wisdom is endless… You can do anything! Thanks again for your help. :)

2. Nice, but take care! If you have more than about 10,000 dataset the combination of vlookup and match can get very slowly (I know, that you know, Dick). In this example new Projects have numbers 90,000 and higher. You can use if(A2>=90000,vlookup(…),A2).