Home RSS feed

 

Excel dynamic sort

Sorting data in-place in MS Excel is easy. This technique however will sort a table without changing the original data. This means you can have several referring tables that will automatically update when the original data is modified. Without macros.

P.S. Use my Online Excel Translator if you want the functions in another language.

This is our initial table:

 ABCDEFGH
1 NameScore     
2 Mallory50     
3 Bob120     
4 Eve10     
5 Alice100     

Let's sort by score. Fist we need to add a column to the left of the data, with the "RANK" of each row. We have to add +COUNTIF(C$2:C4;C5) if the sorted variable (score) is not necessarily uniqe.

 ABCDEFGH
1 NameScore     
23Mallory50     
31Bob120     
44Eve10     
5=RANK(C5;C$2:C$5)Alice100     

Then we add a column with the desired order of data, usually 1,2,3,.. or perhaps a descending order.

 ABCDEFGH
1 NameScore Desired order   
23Mallory50 1Bob120 
31Bob120 2Alice100 
44Eve10 3Mallory50 
52Alice100 4=VLOOKUP(E5;A$2:C$5;2;FALSE)=VLOOKUP(E5;A$2:C$5;3;FALSE) 

That's it!

Now, if score is updated in column C, the sorted list to the right will automatically update. Especially convenient if you want to sort the same data in different ways.

Read more and discuss this page here.

Tags: microsoft, excel

Created on Tue, 07 Apr 2009 00:00

blog comments powered by Disqus