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.
Address: Bd. Decebal, nr. 18, ap. 2
310133 - Arad, jud. Arad, Romania
Phone: +40 357 805 456
E-mail: office@effectiveflux.com
You cand follow us on:
Access the latest information:
Terms and Conditions / Privacy Policy / Cookie Policy