Forum Discussion
dshawSLDC
Jun 19, 2021Brass Contributor
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...
- 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
Jun 19, 2021MVP
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"))
- dshawSLDCJun 20, 2021Brass ContributorThis 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.- SergeiBaklanJun 20, 2021MVP
Formula could be like
=LET( array, SORT(FILTER(FILTER(TableHLP,TableHLP[District]=$A$7),{1,1,0,1,1,1,1,1}),1), k, SEQUENCE(ROWS(array)), m, SEQUENCE(,COLUMNS(array)), r, IF(k<5,INDEX(array,k,m),"remove"), FILTER(r, INDEX(r, 0, 1) <> "remove") )
Creating an array we already filtered columns. After that we check each element of it one by one returning it's value or "remove" depends on each row we check.
In general if return some columns using INDEX that will be like
=INDEX(Table1,SEQUENCE(ROWS(Table1)),{1,3,4})
Please check sample attached.
- dshawSLDCJun 20, 2021Brass Contributor
This is the Formula I ended up with trying to produce the end product I need. I will also attach and example file that shows what i have and what Im trying to obtain. Thanks again for your help.
=LET( mtbl,TableHLP,mtbd,TableHLP[District],dis,B1, array,SORT(FILTER(mtbl,mtbd=dis),1), sf,FILTER(array,{0,0,0,0,1,0,0,0}),af,FILTER(array,{0,0,0,0,0,1,0,0}),rf,FILTER(array,{0,0,0,1,0,0,0,0}),tf,FILTER(array,{0,0,0,0,0,0,1,0}), min,1,max,35, k,SEQUENCE(ROWS(array)),ksf,SEQUENCE(ROWS(sf)),kaf,SEQUENCE(ROWS(af)),krf,SEQUENCE(ROWS(rf)),ktf,SEQUENCE(ROWS(tf)), r,IF((k>=min)*(k<=max),INDEX(array,k),""),rsf,IF((ksf>=min)*(ksf<=max),INDEX(sf,ksf),""),raf,IF((kaf>=min)*(kaf<=max),INDEX(af,kaf),""), rrf,IF((krf>=min)*(krf<=max),INDEX(rf,krf),""),rtf,IF((ktf>=min)*(ktf<=max),INDEX(tf,ktf),""), report,FILTER(r,r<>""),reportser,FILTER(rsf,rsf<>""),reportatt,FILTER(raf,raf<>""),reportrat,FILTER(rrf,rrf<>""),reporttot,FILTER(rtf,rtf<>""), stcnt,SORT(UNIQUE(report)),snct,COUNTA(stcnt), rep,CHOOSE({1,2,3,4,5,6,7},report,reportser,reportatt,"",reportrat,"",reporttot),rep)