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"))
dshawSLDC
Jun 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)
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
- dshawSLDCJun 22, 2021Brass Contributor
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)
- dshawSLDCJun 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 21, 2021Brass Contributor
Wow that formula is way cleaner than mine. Question is there away to drop the name total down to the end line of each Name? Also can we put a blank row in after each name? Thanks again for all your help. I really appreciate it.
- SergeiBaklanJun 21, 2021MVP
Name Total to down could be
=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+2,,0,1), minK, 1, maxK, MIN(35, n), v, INDEX(array,k,col), vPrev, INDEX(array,k-1,col), vNext, INDEX(array,k+1,col), res, IF(k=0, headers, 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")), FILTER(res, (INDEX(res, 0, 1) <> "remove")+(INDEX(res, 0, 1) = "Name")) )
result is
With empty row is more complex, we need to calculate number of names in given block and correctly work with bounds. Maybe next time.
As workaround you may apply conditional formatting to separate blocks with names, as variant