Dynamic Array – FILTER
To extract information from a table according to one or more criteria we can use Advanced Filter. It's a pretty good solution but it has a big problem: it doesn't update. Then we have to intervene with a macro, possibly a "filter" button.
The Dynamic Array solution is much simpler and much more "dynamic": FILTER.
Article dedicated only to Office 365 users.
Here is the file used for example: http://bit.ly/384Lv59
The problem is to extract information from a table into a form.
In the Extras spreadsheet we have the classic version.
I1: I2 Product code for which the filter is made and becomes the Filter Element (CodP)
J1: J2 The calendar date after which the filtering is done and must be the corresponding result of CodP
K1: K2 The single element (obtained by concatenating the previous elements) after which the information is extracted
M1: N4 Data extraction form (you can use VLOOKUP, INDEX and MATCH, or XLOOKUP)
Starting with I4: J4 we have the data extracted using Data - Sort & Filter - Advanced.
Way of working:
1. Choose from I2 CodP
2. Do an advanced filtering (Data - Sort & Filter - Advanced) by checking “Copy to another location”
3. Choose the corresponding date for the CodP on which you want to extract the information
4. View the extracted data.
As I said above, it is a repetitive process that is solved by using a macro.
Or through Dynamic Array.
In the Filter spreadsheet, we have the same working data, except that in I7 we have the FILTER function.
= FILTER (Table1 [[codpa] [Data]]; Table1 [codpa] = I2)
Since I only need two columns of data (CodP and Data), I passed in I6: J6 the table header and in I7 I entered the formula with reference to:
Array - data columns, Include - filtering requirement (column where the data and filtering element are)
To complicate things a bit, I want to extract the data containing CodP, Date, Purchase and Quantity according to two criteria: only from 2017 and the quantity greater than 500.
Under these conditions, the formula in L15 has the following form:
= FILTER (Table1 [[codpa]: [amount]] (YEAR (Table1 [Data]) = L12) (Table1 [amount]> M12))
Where, in Include, we use a logical Boolean expression, (Criterion 1) * (Criterion 2).
Much simpler! Much faster!
Starting with the next article, we move on to POWER QUERY.
The Dynamic Array solution is much simpler and much more "dynamic": FILTER.
Article dedicated only to Office 365 users.
Here is the file used for example: http://bit.ly/384Lv59
The problem is to extract information from a table into a form.
In the Extras spreadsheet we have the classic version.
I1: I2 Product code for which the filter is made and becomes the Filter Element (CodP)
J1: J2 The calendar date after which the filtering is done and must be the corresponding result of CodP
K1: K2 The single element (obtained by concatenating the previous elements) after which the information is extracted
M1: N4 Data extraction form (you can use VLOOKUP, INDEX and MATCH, or XLOOKUP)
Starting with I4: J4 we have the data extracted using Data - Sort & Filter - Advanced.
Way of working:
1. Choose from I2 CodP
2. Do an advanced filtering (Data - Sort & Filter - Advanced) by checking “Copy to another location”
3. Choose the corresponding date for the CodP on which you want to extract the information
4. View the extracted data.
As I said above, it is a repetitive process that is solved by using a macro.
Or through Dynamic Array.
In the Filter spreadsheet, we have the same working data, except that in I7 we have the FILTER function.
= FILTER (Table1 [[codpa] [Data]]; Table1 [codpa] = I2)
Since I only need two columns of data (CodP and Data), I passed in I6: J6 the table header and in I7 I entered the formula with reference to:
Array - data columns, Include - filtering requirement (column where the data and filtering element are)
To complicate things a bit, I want to extract the data containing CodP, Date, Purchase and Quantity according to two criteria: only from 2017 and the quantity greater than 500.
Under these conditions, the formula in L15 has the following form:
= FILTER (Table1 [[codpa]: [amount]] (YEAR (Table1 [Data]) = L12) (Table1 [amount]> M12))
Where, in Include, we use a logical Boolean expression, (Criterion 1) * (Criterion 2).
Much simpler! Much faster!
Starting with the next article, we move on to POWER QUERY.
