Forum Discussion
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...
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"))
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.
=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.
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.
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)If skip repeated names
=LET( array, SORT(FILTER(TableHLP,TableHLP[District]=$B$1)), col, {1,5,6,0,4,0,7,8}, headers, IF(col=0, "", INDEX(TableHLP[#Headers], 1, col)), n, ROWS(array), k, SEQUENCE(n+1,,0,1), minK, 1, maxK, 35, res, IF(k=0, headers, IF( (k>=minK)*(k<=maxK), IF(col=0, "", IF( (INDEX(array,k-1,col)=INDEX(array,k,col))* ((col=1)+(col=8))* (k<>minK), "", INDEX(array,k,col) ) ), "remove")), FILTER(res, (INDEX(res, 0, 1) <> "remove")+(INDEX(res, 0, 1) = "Name")) )which gives
Well I think it is finished. Thank you for all your help. I learned a lot. Let me know what you think. I know its not as elegant as your code and could probably be cleaned up. Let me know if there is anything that could be done simpler. Thanks again.
=LET( array, SORT(FILTER(TableHLP,TableHLP[District]=$B$1)),array1,SORT(FILTER(FILTER(TableHLP,TableHLP[District]=$B$1),{1,0,0,0,0,0,0,0})), columnsToShow, {1,5,6,0,4,0,7,8}, columnNameTotal, 8, lmaxs,1, mins,1,maxs,3, suc, UNIQUE(array1), af,MATCH(array1,suc,0), cn,IF((af>=mins)*(af<=maxs),INDEX(suc,af),"remove"),cnt,FILTER(cn,(INDEX(cn,0,1)<>"remove")), c, ROWS(cnt), cnm,IF((af>=mins)*(af<=lmaxs),INDEX(suc,af),"remove"),cntm,FILTER(cnm,(INDEX(cnm,0,1)<>"remove")), cm, ROWS(cntm), arrayR, ROWS(array), cntseq, SEQUENCE(arrayR,,0,1), minK,IF(lmaxs=1,mins,cm+1), maxK, MIN(c, arrayR), removeArray,IF((cntseq>=minK)*(cntseq<=maxK),INDEX(array,cntseq,{1,2,3,4,5,6,7,8}),"remove"), trunkArray,FILTER(removeArray,(INDEX(removeArray,0,1)<>"remove")+(INDEX(removeArray,0,1)= "Name")), arrayRows, ROWS(trunkArray), names, INDEX(trunkArray, SEQUENCE(arrayRows),1), distinct, UNIQUE(names), distinctRows, ROWS(distinct), startPos, INDEX( XMATCH(distinct, names), SEQUENCE(,distinctRows-1,2)) + SEQUENCE(,distinctRows-1,0), enumPos, SEQUENCE( COLUMNS(startPos),,1,0), k, SEQUENCE(arrayRows+distinctRows-1), i, k - MMULT(--(k>startPos), enumPos ), emptyPos, MMULT(--(k=startPos), enumPos ), v, IF(columnsToShow=0,"",INDEX(trunkArray, i, columnsToShow)), vPrev, INDEX(trunkArray, i-1, columnsToShow), vNext, INDEX(trunkArray, i+1, columnsToShow), res, IF( emptyPos, "", IF( (vPrev=v)*(columnsToShow=1)*(i<>1), "", IF( i < arrayRows, IF( (vNext=v)*(columnsToShow=columnNameTotal), "", v), v) ) ), res)
From my point of view that's bit overcomplicated, same result gives
=LET( array, FILTER(TableHLP,TableHLP[District]=$B$1), k, SEQUENCE(ROWS(array)), minK, 1, maxK, 30, col, {1,5,6,0,4,0,7,8}, res, IF( (k>=minK)*(k<=maxK), IF(col=0, "", INDEX(array,k,col)), "remove"), FILTER(res, INDEX(res, 0, 1) <> "remove"))please check in attached file.
Not sure right now how to empty repeated names, will play with it some later.
