Forum Discussion
How to split table?
Hi , can i ask for help? is there a way or formula that can split tables with filter? Like this for example?
From this :
To This :
Or if possible, separate them by "LEVEL" into separate sheets?
PS : i am using Microsoft Excel 2019
Thank you so much!
Separate sheets might be easier because each sheet has a different formula and, also, one partial table cannot overlap the next. The problem is that, in order to use recent methods, 2019 has to be removed in favour of using 365. Then, the formula
= DROP( REDUCE({"",""}, UNIQUE(Level), LAMBDA(acc,grd, LET( headerRow, {"","","","";"Date","Name","Level","Amount"}, selected, FILTER(table, Level=grd), total, SUM(TAKE(selected, ,-1)), totalRow, HSTACK("Total","","", total), VSTACK(acc, headerRow, selected, totalRow) ) ) ), 2)will give
as a single output, with a bit of conditional formatting!
RanzieJ You can certainly do this and get "nearly" what you are looking for by using the Show Report Filter trick.
For your example, drop the "level" field in the Report Filter after you have laid out your Pivot Table.
Here is how: https://www.myexcelonline.com/blog/show-report-filter-pages-in-an-excel-pivot-table/
Thanks,
John Michaloudis
Microsoft MVP
- Good idea. It's easy to overlook such functionality!
If you had 365, you could create a simple function to pull certain records from the table.
'SplitTable Lambda =LAMBDA(level,LET( header, Table1[#Headers], filtered, SORT(FILTER(Table1, Table1[Level] = level, ""), {1, 2}), VSTACK(header, filtered) ))Then call the function where needed in a sheet:
Unfortunately, Excel 2019 does not support dynamic arrays nor Lambda. It's still possible with a ctrl+shift+enter array (INDEX-SMALL) but it's not an elegant solution. Using the Advanced Filter might be a better way to go in 2019.
Hi RanzieJ,
As far as I know there is no formula that would automatically separate your table. For that you would need to write VBA/Macros.
One simple way you make "multilayered table" is to source table that you mentioned and insert pivot table. Choose LEVEL field and put for Rows and then add the rest fields for Rows. Amount field would be in Values.
