Category Archives: filtering

#Excel Super Links #100 (Special Edition) – shared by David Hager

 

My Top 10 Most Underviewed Posts (IMO)

Please check out these posts on my website. There are some powerful techniques to be found here.

#Excel: Getting the Sunrise and Sunrise Times From an Address by David Hager

https://dhexcel1.wordpress.com/2017/07/11/excel-getting-the-sunrise-and-sunrise-times-from-an-address-by-david-hager/

#Excel: Removing Outliers with Excel Formulas to Modify Control Limits by David Hager

https://dhexcel1.wordpress.com/2017/05/17/excel-modifying-control-chart-data-to-remove-outliers-with-excel-formulas-by-david-hager/

#Excel: Using Conditional Formatting to Highlight 3D Formulas with Defined Names by David Hager

https://dhexcel1.wordpress.com/2017/04/25/excel-using-conditional-formatting-to-highlight-3d-formulas-with-defined-names-by-david-hager/

Getting the Latest Earthquake Alert Using the WEBSERVICE and FILTERXML Functions in #Excel by David Hager

https://dhexcel1.wordpress.com/2017/07/10/getting-the-latest-earthquake-alert-using-the-webservice-and-filterxml-functions-in-excel-by-david-hager/

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

https://dhexcel1.wordpress.com/2017/01/08/creating-a-unique-delimited-string-from-an-excel-filtered-list-by-using-the-textjoin-function-by-david-hager/

Excel: Conditional Format Rows in List 1 that are Not in List 2 by David Hager

https://dhexcel1.wordpress.com/2017/06/25/excel-conditional-format-rows-in-list-1-that-are-not-in-list-2-by-david-hager/

#Excel Short and Sweet Tip #17 (Returning a Letter Grade Based on a Normal Grading Scale Without Lookup Table) by David Hager

https://dhexcel1.wordpress.com/2017/05/26/excel-short-and-sweet-tip-16-returning-a-letter-grade-based-on-a-normal-grading-scale-without-lookup-table-by-david-hager/

The DAX ENDSWITHX Function Equivalent

https://dhexcel1.wordpress.com/2015/06/21/the-dax-endswithx-function-equivalent/

Returning the First Temperature in PowerPivot

https://dhexcel1.wordpress.com/2014/05/25/returning-the-first-temperature-in-powerpivot/

#Excel Short and Sweet Tip #25 (Random Sound – Worksheet UDF) by David Hager

https://dhexcel1.wordpress.com/2017/07/07/excel-short-and-sweet-tip-25-playing-a-random-sound-with-a-worksheet-udf-by-david-hager/

 

 

#Excel Super Links #91 – shared by David Hager

 

Copy a Data Table from PDF into Excel

https://exceloffthegrid.com/copy-a-data-table-from-pdf-into-excel/

Filtering PivotTables with VBA? Deselect Slicers First!

http://dailydoseofexcel.com/archives/2015/11/17/filtering-pivottables-with-vba-deselect-slicers-first/

Use Multiple Connections Between Power BI Tables

https://powerbi.tips/2017/07/use-multiple-connections-between-tables/

Database Functions in Excel

https://roymacleanvba.wordpress.com/2010/09/21/database-functions-in-excel/

Conditional Formatting Gem in #Excel: Highlight the N Closest Values to the Mean of a Range in a Filtered List by David Hager

https://dhexcel1.wordpress.com/2017/07/05/conditional-formatting-gem-in-excel-highlight-the-n-closest-values-to-the-mean-of-a-range-in-a-filtered-list-by-david-hager/

#Excel Super Links #61-90: > 150 Excel and BI Links

 

I had mentioned previously that I used this wonderful Word technique to combine 30 Word files to make these collections.

https://www.dickinson.edu/download/downloads/id/2534/word_combine_files_pdf

One thing I did not mention was that there was a lot of blank space creating during the file consolidation. On the previous 2 ESL collections the space was laboriously removed manually. Not wanting to have to do this again, I searched the net and found this comment in a discussion about removing blank space from Word documents.

 

“Replacing ^p^p with ^p actually is the proper way to remove empty paragraphs.

However, when my documents contains e.g. ^p^p^p^p or similar, it won’t pick them all up and will again leave ^p^p. So I’ve to run this search/replace operation a few times until all cases have been covered.”

 

In order to get rid of all of the empty paragraghs, I needed 8 repeat operations of this type. You can download the resulting Word file containing >250 links to great Excel and Power BI content here.

Excel Super Links 61-90

Conditional Formatting Gem in #Excel: Highlight the N Closest Values to the Mean of a Range in a Filtered List by David Hager

 

I am extending the closest value technique I published recently to calculate the same based on a filtered list.

https://dhexcel1.wordpress.com/2017/07/04/using-conditional-formatting-in-excel-to-highlight-the-n-closest-values-to-the-mean-of-a-range-by-david-hager/

In this demonstration, the goal is to highlight values in a numeric range that are clostest to the average of that range in a filtered list. So, we first make the range dynamic with the following defined name formula.

NumRange =OFFSET(Sheet1!$A$1,1,,COUNTA(Sheet1!$A:$A)-1)

Then, we modify that range to include only filtered values.

fNumRange =IFERROR(IF(SUBTOTAL(3,OFFSET(NumRange,ROW(NumRange)-MIN(ROW(NumRange)),,1)),NumRange,””),””)

For more information on the SUBTOATL function as used here, see:

https://dhexcel1.wordpress.com/2017/01/08/creating-a-unique-delimited-string-from-an-excel-filtered-list-by-using-the-textjoin-function-by-david-hager/

Next, we use that range to make an array of the absolute differences of each value of the range from the average.

ABS_Range =IFERROR(ABS(fNumRange-AVERAGE(fNumRange)),””)

We can then define a cell for the number of values to highlight.

N_Values =$B$2

The heavy work is done by the next formula, which creates an array of the N values to be higlighted.

Num_Array=INDEX(NumRange,N(IF(1,TRANSPOSE(MATCH(SMALL(ABS_Range,ROW(

INDIRECT(“1:”&N_Values))),ABS_Range,0)))))

This formula returns the position of each smallest deviation in the 2nd argument of the INDEX function, which then returns the values corresponding to those deviations, based on a filtered list. The use of the formula syntax needed to do this with the INDEX function is explained at the following link.

https://excelxor.com/2014/09/05/index-returning-an-array-of-values/

This formula can now be used in the creation of the CF, where CF Formula is =SUM(N(A1=Num_Array)), starting at A1 and applied to all of Column A.

The result of this CF is shown below.

CF_Closest_Filtered1

The example file can be downloaded here.

CF_Closest_Filtered

Advanced Filter Magic by David Hager

 

Previously, I discussed the use of a custom list with Data Advanced Filter.

https://dhexcel1.wordpress.com/2017/04/14/excel-using-advanced-filter-with-a-custom-list-by-david-hager/

In the article, I only showed one custom list being used in the advanced filter. However, in most situations, a number of specific searches are required. To illustrate this, I combined all of the links from ESL #1-60 in an Excel file. The description of the links are in column A and the links are in column B. The goal is to perform multiple searches with lists while using only a single formula in the advanced filter. A picture of this can be seen in the following figure.

 MAF3

The formula in the advanced filter (B1:B2) on the AdvFilterTable tab is

=SUMPRODUCT(NOT(ISERROR(SEARCH(Choice,A5)))+0)>0

where Choice=CHOOSE(MATCH(AdvFilterTable!$A$2,CritLists,0),PowerBIList,VBAList,ExcelList)

This is the key formula for returning the desired list into the advanced filter formula.It is controlled by cell A2, which uses Data Validation with the following list.

CritLists=LList!$E$2:$E$4

The criteria lists tied to the CHOOSE function are are shown in the following figure.

 MAF2

These are expanding dynamic lists to add additional criteria if necessary.

PowerBIList=OFFSET(LList!$A$1,1,,COUNTA(LList!$A:$A)-1)

VBAList =OFFSET(LList!$B$1,1,,COUNTA(LList!$B:$B)-1)

ExcelList =OFFSET(LList!$C$1,1,,COUNTA(LList!$C:$C)-1)

When a list is selected in cell A2 and the advanced filter is applied to the links table, the result is as shown below.

 MAF1

Functionally, you can search all of my links for the content you desire. Also, you can use this filter model with your own data and criteria. I hope that this is useful.

You can download the file here.

ESL1-60_Filter

 

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.