Excel Tip # 24: Copying Text While Sorting

Do you ever need to sort text without changing the original range? You know: to have an original unsorted range and a sorted one that works in parallel?

This is a variation on the method using =index(), =match(), and =small() that can be used for numbers found in the Chapter 4 of Powell and Baker’s text. The thing is, those are functions that operate on numbers, not text.*

The trick is to use =countif(). This works with numbers or text, and can allow you to count the number of entries in a range that fall alphabetically before the cell that you’re in.

So if you have a range of unsorted text in A1.A5, you can create a list of numbers giving their rank order in B1.B5 by entering this formula in B1 and copying it down the list:

=countif($A$1.$A$5,”<=”&A1)

The first argument is telling =countif() to work on that fixed range of text you want to sort.

The second argument uses the ampersand as a text concatenator to combine less than or equal to with the contents of the first cell in your range.

Your result will look something like this:

Management	4
Economics	2
Marketing	5
Accounting	1
Finance	3

Now you can use those ranks to put your sorted text anywhere you like. The site I linked above advocates using =vlookup(), but I use =offset() too.

* I never thought that section was very useful. Then I talked to MBA students who have jobs where they have ranges they need to sort in spreadsheets they’re not supposed to goof up (thanks MU!). Now that they’ve opened up my mind a bit, I have found a few cases where I’ve been sorting and desorting that could be improved by creating a parallel sorted range.

Technorati Tags: sort,text,excel