Forum Discussion

Brass Contributor

Jun 18, 2021

Solved

Excel Spill Row Split

I am wondering if there is away to Split the Spill that comes from the Following ARRAY. =SORT(FILTER(FILTER(TableHLP,TableHLP[District]=$A$7),{1,1,0,0,0,0,0,0}),1) It produces a list of names and s...

excel

Formulas and Functions

Jun 19, 2021

On each sheet you may use formulas like

=LET(k, SEQUENCE(ROWS(array)),
     r, IF( (k >= minN)*(k <= maxN),
            INDEX(array,k),
            "remove"),
     FILTER(r, r <> "remove"))

SergeiBaklan

Diamond Contributor

Jun 19, 2021

dshawSLDC

On each sheet you may use formulas like

=LET(k, SEQUENCE(ROWS(array)),
     r, IF( (k >= minN)*(k <= maxN),
            INDEX(array,k),
            "remove"),
     FILTER(r, r <> "remove"))

dshawSLDC

Brass Contributor

Jun 19, 2021

This works for splitting across multiple sheets but the array is only showing 1 column. How do i get it to show all column's. everything i try hasn't worked. This is the formula i have been using.

=LET(k, SEQUENCE(ROWS(SORT(FILTER(FILTER(TableHLP,TableHLP[District]='Invoice (2)'!$A$7),{1,1,0,1,1,1,1,1}),1))),
r, IF( (k >= 1)*(k <= 30),
INDEX(SORT(FILTER(FILTER(TableHLP,TableHLP[District]='Invoice (2)'!$A$7),{1,1,0,1,1,1,1,1}),1),k),
"remove"),
FILTER(r, r <> "remove"))

Thanks again for you help.