Category Archives: filtering

Using the CHOOSE and AGGREGATE Functions To Apply Conditional Formatting to a Filtered List in Excel By David Hager

The very 1st article I wrote for my blog was about highlighting values in a filtered list. I decided that it was time to revisit that subject. Specifically, I had written an article for Rob Collie about using the CHOOSE function with conditional formatting (CF) in Excel. It turned out that the article was too complex to be useful.

https://dhexcel1.wordpress.com/2013/12/19/highlighting-top-n-values-in-a-filtered-table-column-using-conditional-formatting/

https://powerpivotpro.com/2012/11/david-hager-on-dynamic-conditional-formatting/

So, I decided to incorporate the use of the CHOOSE function into my original article and give it a friendly user interface. The two user inputs were both created using Data Validation with the List option. They are defined as shown below and pictured in the following figure (showing unfiltered list).

critunfilt

TopN=Sheet1!$B$1

Crit=Sheet1!$B$2

These inputs are used to return values needed by the CF conditions. TopN is selected by the user in cell B1. Cell B2 gives a choice of Large or Small. The following formula converts this information into a number to be used by the CHOOSE function.

CritNumber=IF(Crit=”Large”,1,2)

The CF conditions are made to be applied to column B. When each formula is defined, the active cell needs to be B6.

LargeCondition=OR(Sheet1!B6>=AGGREGATE(14,1,Sheet1!$B$6:$B$31,ROW(INDIRECT(“$1:$”&TopN))))

critlarge

SmallCondition=OR(Sheet1!B6<=AGGREGATE(15,1,Sheet1!$B$6:$B$31,ROW(INDIRECT(“$1:$”&TopN))))

critsmall

When combined with the CHOOSE function (the formula used for the CF), they provide a methodology to highlight filtered rows, as shown below (filter set to display values of 50 to 175 in column B).

=CHOOSE(CritNumber,LargeCondition,SmallCondition)

If a way to visualize TopN and BottomN simultaneously was required, a second CF condition can be appled to column B.

=CHOOSE(CritNumber, SmallCondition, LargeCondition)

The following figure shows the result.

critboth

This is just a demonstration of the versatility of using CF condtions in filtered lists. The application of this method to many columns can be done, as well as the use of different CF conditions. I might write another article about this subject later, but for now, enjoy! Click the link below to download the working file.

aggregate_filter_choose

Creating a Unique Delimited String from an Excel Filtered List by Using the TEXTJOIN Function By David Hager

Some time ago (16 years – which is 100 in Excel years) I developed a formula solution for counting the number of unique items in a filtered list. I realized that this methodology could be used with the new TEXTJOIN function in Excel (Office 365 version). See the original publication in EEE# 20:

https://dhexcel1.wordpress.com/2017/01/07/archive-of-excel-experts-e-letter-by-david-hager/

The following defined names are needed to construct the formula.

Rge=Sheet1!$A$2:$A$20

unRge=IF(SUBTOTAL(3,OFFSET(Rge,ROW(Rge)-MIN(ROW(Rge)),,1)),Rge,””)

unRge returns an array that contains only filtered items.

tj_subtotal

In the figure, rows 2,3,4,6,8,10,11,12,15,17,18,19,and 20 are visible. To return a unique delimited string for only those visible rows, use the following formula (in E3):

=TEXTJOIN(“,”,TRUE,IF(N(IF(ISNA(MATCH(“”,unRge,0)),MATCH(Rge,Rge,0),IF(MATCH(unRge,unRge,0)=MATCH(“”,unRge,0),0,MATCH(unRge,unRge,0)))=ROW(Rge)-MIN(ROW(Rge))+1),unRge,””))

which affords

a,c,h,l,v,m,d,w,g,o,t

You can download the file here.

textjoin_subtotal1

Remember again, that you have to have the Excel version included in Office 365 in order for the TEXTJOIN formula to work.