Excel 2010 Bible

Part II: Working with Formulas and Functions

396

FIGURE 17.10

A multicell array formula displays the entries in A4:A13 in reverse order.

Sorting a range of values dynamically ......................................................................

Figure 17.11 shows a data entry range in column A (named Data). As the user enters values into

that range, the values are displayed sorted from largest to smallest in column C. The array formula

in column C is rather simple:

{=LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data))))}

If you prefer to avoid the #NUM! error display, the formula gets a bit more complex:

{=IF(ISERR(LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data))))),

“”,LARGE(Data,ROW(INDIRECT(“1:”&ROWS(Data)))))}

Note that this formula works only with values. The companion CD-ROM has a similar array for-

mula example that works only with text.

Returning a list of unique items in a range ..............................................................

If you have a single-column range named Data, the following array formula returns a list of the

unique items in the range (the list with no duplicated items):

{=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT

(“1:”&ROWS(Data))),MATCH(Data,Data,0),””),ROW(INDIRECT

(“1:”&ROWS(Data)))))}

This formula doesn’t work if the Data range contains any blank cells. The unfilled cells of the array

formula display #NUM!.