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"))
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)