Excel: How to Sort Data with Each Recalculation

ID: Q77380

The information in this article applies to:

Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0
Microsoft Excel for OS/2, versions 2.2, 3.0
Microsoft Excel for the Macintosh, versions 2.x, 3.0, 4.0, 5.0

SUMMARY

By using a special adaptation of the MIN() function as an array, you can create a list sorted in ascending order that will re-sort each time the Microsoft Excel worksheet is recalculated. This formula is useful in cases where the original unsorted data may frequently change.

MORE INFORMATION

To simplify the discussion of this technique, an assumption is made here that the data to be sorted is in a single column and the sorted version of this data will be located in an adjacent column and sorted in ascending order.

Example for versions 3.0, 4.0, 5.0

Enter the following into a worksheet:


      A1:   49     B1:  =MIN($A$1:$A$5)
      A2:    7     B2:
      A3:    5     B3:
      A4:    4     B4:
      A5:   72     B5:

Select cells A1:A5 and follow the appropriate procedure below to give cells A1:A5 the defined name "Orig":

Microsoft Excel 5.0
-------------------

From the Insert menu, choose Name, and then choose Define.

Microsoft Excel versions earlier than 5.0
-----------------------------------------

Choose Define Name from the Formula menu.

In the Name box, type Orig and choose the OK button. "Orig" stands for original data.

In cell B2, enter the following formula:

{=MIN(IF(Orig>B1,Orig),IF(SUM(IF(Orig=B1,1))>
SUM(IF($B$1:B1=B1,1)),B1,MAX(Orig)))}

NOTE: The above formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

Select cells B2:B5 and choose Fill (Fill Down in version 4.0) from the Edit menu.

The resulting data should resemble the following example:


   A1: 49          B1:  4
   A2:  7          B2:  5
   A3:  5          B3:  7
   A4:  4          B4: 49
   A5: 72          B5: 72

If you change the value in cell A2 from 7 to 96, the sorted values in column B will reflect this change.

NOTE: If the Calculation option for your worksheet is set to Manual, you will need to calculate the worksheet to see this change.

This kind of dynamic sort can also be performed in descending order. To do this, use the MAX() function in place of all MIN() functions, use a less than operator (<) in place of the first greater than operator (>), and change the single MAX() function to MIN(), as in the following example:


   {=MAX(IF(orig<B1,orig),IF(SUM(IF(orig=B1,1))>
   SUM(IF(B$1:B1=B1,1)),B1,MIN(orig)))}

This formula can be simplified by leaving out the entire second argument to the IF() statement (in either the MAX() or MIN() case). However, this simplification will produce an incorrect sort if there are any duplicates in the original data set.

Example for Microsoft Excel 4.0 and later

In Microsoft Excel version 4.0and greater, you can use the SMALL() and LARGE() functions. For example, SMALL($A$1:$A$5,1) returns the smallest value in the range and LARGE($A$1:$A$5,1) returns the largest value in the range. To produce a sorted list, you can use the following function:


   =SMALL($A$1:$A$8,ROW())

where $A$1:$A$8 is the range that contains your numbers to be sorted and assumes that your formula starts in row 1. If your formula does not start in row 1, but rather in row 5, change the formula to read:


   =SMALL($A$1:$A$8,ROW()-4))

Another alternative is to create another column with the numbers 1 through the maximum number of entries in the range to be sorted. For example, if you have created the numbered list starting in cell B1, you can refer to these values in the formula


   =SMALL(RANGE,B1)

where B1 is 1, B2 is 2, B3 is 3, and so on.

REFERENCES

"User's Guide 1," version 4.0, pages 134-137, 157-166
"Function Reference," version 4.0, pages 227-228, 268-269
"User's Guide," version 3.0, pages 115-120, 280-289
"Function Reference," version 3.0, pages 126-127, 149, 152

Additional query words: 2.00 2.01 2.10 2.20 2.21 resort

Keywords :
Version :
Platform :
Issue type :