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 21, 2021MVP
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
dshawSLDC
Jun 22, 2021Brass Contributor
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.
- SergeiBaklanJun 22, 2021MVP
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 )
- dshawSLDCJun 22, 2021Brass Contributor
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)
- dshawSLDCJun 22, 2021Brass Contributor
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.