# How to split table?

Copper Contributor

# 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!

6 Replies

# Re: How to split table?

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.

# Re: How to split table?

If you had 365, you could create a simple function to pull certain records from the table.

``````'SplitTable Lambda
=LAMBDA(level,LET(
filtered, SORT(FILTER(Table1, Table1[Level] = level, ""), {1, 2}),
))``````

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.

# Re: How to split table?

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(
selected,  FILTER(table, Level=grd),
total,     SUM(TAKE(selected, ,-1)),
totalRow,  HSTACK("Total","","", total),
)
)
),
2)``````

will give

as a single output, with a bit of conditional formatting!

# Re: How to split table?

@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.

Thanks,

John Michaloudis

Microsoft MVP

# Re: How to split table?

Good idea. It's easy to overlook such functionality!