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