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