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
Thanks for your assistance again. I updated the code to count names to provide the min and max that way is doesn't cut off services. The only thing left is to add the row spacing. trying to use the same count function to do this. this is the code Im trying to use.
afu,UNIQUE(af),
cc,IF((af>=mins)*(af<=maxs),INDEX(afu,af),"remove"),ccr,FILTER(cc,(INDEX(cc,0,1)<>"remove")),Im trying to take afu and add those to ccr to get the extra spacing.
The following is the updated code with the counting.
=LET(
array, SORT(FILTER(TableHLP,TableHLP[District]=$A$7)),array1,SORT(FILTER(FILTER(TableHLP,TableHLP[District]=$A$7),{1,0,0,0,0,0,0,0})),
col, {1,5,6,0,4,0,7,8},
mins,1,maxs,8,
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),
afu,UNIQUE(af),
cc,IF((af>=mins)*(af<=maxs),INDEX(afu,af),"remove"),ccr,FILTER(cc,(INDEX(cc,0,1)<>"remove")),
n, ROWS(array),
k, SEQUENCE(n+2,,0,1),
minK, 1, maxK, MIN(c, n),
v, INDEX(array,k,col),
vPrev, INDEX(array,k-1,col),
vNext, INDEX(array,k+1,col),
res, IF( (k>=minK)*(k<=maxK),
IF(col=0, "",
IF( (vPrev=v)*(col=1)* (k<>minK),
"",
IF( k<maxK,
IF( (vNext=v)*(col=8), "", v ),
v )
) ),
"remove"),
Final,FILTER(res, (INDEX(res, 0, 1) <> "remove")+(INDEX(res, 0, 1) = "Name")),
Final
)I also attached the example with the updated code.
Thanks again.
I did that on mockup with the table (one block)
formula is
=LET( array, Table1, columnsToShow, {1,2,4}, columnNameTotal, 4, arrayRows, ROWS(array), names, INDEX(array, 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, INDEX(array, i, columnsToShow), vPrev, INDEX(array, i-1, columnsToShow), vNext, INDEX(array, i+1, columnsToShow), result, IF( emptyPos, "", IF( (vPrev=v)*(columnsToShow=1)*(i<>1), "", IF( i < arrayRows, IF( (vNext=v)*(columnsToShow=columnNameTotal), "", v), v) ) ), result )Thank you very much for all your help. I took what you had there and added the ability to control how many names appear on 1 sheet. I'm sure there is a more eloquent way of doing this. take a look and let me know what you think. Again thank you.
=LET( array, SORT(FILTER(TableHLP,TableHLP[District]=$A$7)),array1,SORT(FILTER(FILTER(TableHLP,TableHLP[District]=$A$7),{1,0,0,0,0,0,0,0})), columnsToShow, {1,5,6,0,4,0,7,8}, columnNameTotal, 4, lmaxs,1, mins,1,maxs,8, 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, 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)I did find an error in my code.
columnsToShow, {1,5,6,0,4,0,7,8},I the 0 will return the name. Im trying to figure out how to prevent that.