Welcome to Excel Jet Consult blog post. In this tutorial, we are going to continue in learning how to Append Filtered List using Single Cell Worksheet Functions in Excel. Let’s dive in
Dataset
We are going to use the same dataset used to perform the same operation using the new PY function as seen below. Note the three tables are formatted officially as an Excel Table using the CTRL + T. The names of each Tables are: Sales2021, Sales2022 and Sales2023 respectively and that can be found in the Table Design contextual ribbon tab.
Append Filtered List using Single Cell Worksheet Functions
To append filtered list using single-cell worksheet functions:
- Execute this query in cell L1: =LET(
list1,sales2021,list2,sales2022,list3,sales2023,
sales2021SalesColumn,sales2021[Sales],sales2022SalesColumn,sales2022[Sales],sales2023SalesColumn,sales2023[Sales],
sales2021SalesColumn_filtered,FILTER(list1,sales2021SalesColumn>=15000),
sales2022SalesColumn_filtered,FILTER(list2,sales2022SalesColumn>=15000),
sales2023SalesColumn_filtered,FILTER(list3,sales2023SalesColumn>=15000),
append,VSTACK(sales2021SalesColumn_filtered,sales2022SalesColumn_filtered,sales2023SalesColumn_filtered),append) - Click Enter to commit the formula into cell L1
Explanation:
We used LET function to define and assign names (variables) to various parts of the formula to make it more readable and manageable. The following names were defined list1, list2, list3, sales2021SalesColumn, sales2022SalesColumn, sales2023SalesColumn, sales2021SalesColumn_filtered, sales2022SalesColumn_filtered, sales2023SalesColumn_filtered, and append
Next, we used the Dynamic Array FILTER function to apply a filter to each of the sales columns, selecting only the rows where the sales value is greater than or equal to 15,000 for each respective year. The filtered columns are stored in variables with names like sales2021SalesColumn_filtered, sales2022SalesColumn_filtered, and sales2023SalesColumn_filtered.
After filtering the sales data for each year, we used the new VSTACK function to append (stacks vertically) the filtered sales columns into a single column named append.
From the screenshot below, we achieved the filtering and appending of the three tables based on Sales that is greater than or equal to 10,000. From the status bar located at the bottom right of the screenshot, the SUM of the Sales columns across the three tables based on the specified criteria is £5,722,537 which is consistent with the result we achieved using the new PY function.
The interesting thing is that when new data in added to any of the three tables, the formula will automatically update. See you in the next blog post