Filter()
(This is for Daniel, who needs help with his Excel...)
The filter() function is mighty. I'll try to explain it with our existing data. You need to refer to my blog entry, Excel's Dynamic Arrays (Part 1). Let me explain the syntax, then we'll do some interesting things. In our previous blog entry, our data was stored in Data!A1:U2275. We'll use that in this blog example as well.
The first thing that we will do is to create a table (tbl_Honey) of our data in Data!A1:U2275. Next let's create the tbl_Honey headers in A5.
=tbl_Honey[#Headers]Bold the headers (A5:U5)
Now let's get started with Filter(). The syntax is:
filter(<the range to filter>, <criteria>)Let's say we want to filter our data where the County = "MADISON". In A6, enter:
=FILTER(tbl_Honey,tbl_Honey[County]="MADISON")
This filter() formula says to look at the table tbl_Honey where tbl_Honey[County] = "MADISON". This is honey information for Madison County, Kentucky. Pretty easy.
AND and OR
That was easy. Let's say we want the criteria to be where County = "MADISON" and Year = 2017. You may think we would just enter:
=FILTER(tbl_Honey, AND(tbl_Honey[County]="MADISON", tbl_Honey[Year]=2017))But that would be incorrect.
Microsoft has modified the criteria section to be enclosed by "()" and uses "*" (AND) and "+" (OR). So, if we want County = "MADISON" and Year = 2017 you have to enter:
=FILTER(tbl_Honey,(tbl_Honey[County]="MADISON")*(tbl_Honey[Year]=2017))
Each criterion has to be enclosed by parenthesis, and if you want an AND, you need to use "*". If you want an OR, you need to use "+".
County = "MADISON" and Year = 2017:
=FILTER(tbl_Honey,(tbl_Honey[County]="MADISON")*(tbl_Honey[Year]=2017))County = "MADISON" or County = "FAYETTE":
=FILTER(tbl_Honey,(tbl_Honey[County]="MADISON")+(tbl_Honey[County]="FAYETTE"))
That's interesting, but...
Advanced
Remember our Sort() function from Part 1? Let's sort it on County (column 10). In A6 enter:
=SORT(FILTER(tbl_Honey,(tbl_Honey[County]="MADISON")+(tbl_Honey[County]="FAYETTE")), 10,1)
That's better, but as we investigate the data we're really only interested in:
- Period = "YEAR" (column 2)
- Data Item = "HONEY - PRODUCTION, MEASURED IN LB" (column 17)
- County = "MADISON" or "FAYETTE" (column 10)
=FILTER(tbl_Honey,(tbl_Honey[Period]="YEAR")*(tbl_Honey[Data Item]="HONEY - PRODUCTION, MEASURED IN LB")*((tbl_Honey[County]="MADISON")+(tbl_Honey[County]="FAYETTE")))
That's much better. Notice that we had to put an extra parenthesis around the OR criteria ((tbl_Honey[County]="MADISON")+(tbl_Honey[County]="FAYETTE")). But it can still be improved by sorting it ascending on County and descending on Year.
=SORT(FILTER(tbl_Honey,(tbl_Honey[Period]="YEAR")*(tbl_Honey[Data Item]="HONEY - PRODUCTION, MEASURED IN LB")*((tbl_Honey[County]="MADISON")+(tbl_Honey[County]="FAYETTE"))), {10,2},{1,-1})
Notice that you need to place {10,2} for the columns that you want to sort by, then {-1, 1} for ascending column 10 (County), and descending column 2 (Year).
Let's save the file and move on to Part 3.
