Welcome to Excel Jet Consult blog post. We previously learn how to split values into multiple columns using FILTERXLM Function. In this tutorial, we will consider how we can write a robust and easily understandable formula that will dynamically append tables across sheet sheets using the FILTERXML Function. Let’s get started
In the caption below, we have Sales Report for 2017 to 2018
There are six columns in each of the sheet tabs. In Summary sheet, we want to combine the report from Sales for 2017 and Sales for 2018 respectively. Performing this operation using Power Query is perhaps the only method known by users who have knowledge of Power Query. But, I am going to show us how to use formula to append the tables without the use of Power Query!
Format Data As Excel Tables
Since we want to make the append to be dynamic in the sense that when new data is added in any of the source data, it will automatically update, hence, we are going to format Sales for 2017 and Sales for 2018 as Excel Tables.
- Click inside the dataset in each of Sales for 2017 sheet tab and execute CTRL + T
- In the Create Table dialogue box as seen below
- Click on OK
- Repeat the above steps for the Sales 2018
Descriptive Table Names
By default, Excel uses Table1 and Table2 for each of the Table created. We want to provide descriptive names for our Tables.
- In the Properties group of the Table Design tab, click inside Table1 and for this example, we provided Sales2017 as the official Table name
Repeat the steps above and name the Table as Sales2018. Note, its important to hit Enter after typing the Table name
The Excel FILTERXML returns specified data from the XML content by using the specified XPATH
FILTERXML Syntax: =FILTERXML(xml,xpath)
xml – exTensible Markup Language
xpath – XML Path Language
- In cell A2 of the Summary sheet, execute: =INDEX(FILTERXML(“<jerry><tom>”&TEXTJOIN(“</tom><tom>”,TRUE,Sales2017,Sales2018)&”</tom></jerry>”,”//tom”),SEQUENCE(COUNTA(Sales2017[Date],Sales2018[Date]),6))
- Click Enter.
Note, you need to have Dynamic Array SEQUENCE Function for this formula to work. In another video, I will show you how to write the formula without need for DA SEQUENCE Function
In the caption below, we can see that the data from the two sheets have been appended and we have 288 rows
We have seen that it is possible to append data using FILTERXML Function