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.
Everyone was impressed. I tell them your excel wisdom is endless… You can do anything! Thanks again for your help. :)
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).
Some readers might not remember this helpful article:
http://www.dailydoseofexcel.com/archives/2009/04/20/vlookup/
Excellent point Tobias.
To achieve sorting by project number while keeping projects with different numbers together, create a new column for sorting and establish a lookup table mapping old and new project numbers.