Forum Discussion

dshawSLDC's avatar
dshawSLDC
Brass Contributor
Jun 19, 2021

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...
Excel
Formulas and Functions
Macros and VBA
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Jun 19, 2021

    dshawSLDC 

    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's avatar
SergeiBaklan
MVP

dshawSLDC 

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's avatar
dshawSLDC
Brass Contributor
Jun 20, 2021
This works for splitting across multiple sheets but the array is only showing 1 column. How do i get it to show all column's. everything i try hasn't worked. This is the formula i have been using.

=LET(k, SEQUENCE(ROWS(SORT(FILTER(FILTER(TableHLP,TableHLP[District]='Invoice (2)'!$A$7),{1,1,0,1,1,1,1,1}),1))),
r, IF( (k >= 1)*(k <= 30),
INDEX(SORT(FILTER(FILTER(TableHLP,TableHLP[District]='Invoice (2)'!$A$7),{1,1,0,1,1,1,1,1}),1),k),
"remove"),
FILTER(r, r <> "remove"))

Thanks again for you help.
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jun 20, 2021

    dshawSLDC 

    Formula could be like

    =LET(
array, SORT(FILTER(FILTER(TableHLP,TableHLP[District]=$A$7),{1,1,0,1,1,1,1,1}),1),
k,     SEQUENCE(ROWS(array)),
m,     SEQUENCE(,COLUMNS(array)),
r,     IF(k<5,INDEX(array,k,m),"remove"),
FILTER(r, INDEX(r, 0, 1) <> "remove")
)

    Creating an array we already filtered columns.  After that we check each element of it one by one returning it's value or "remove" depends on each row we check.

     

    In general if return some columns using INDEX that will be like

    =INDEX(Table1,SEQUENCE(ROWS(Table1)),{1,3,4})

     

    Please check sample attached.

    • dshawSLDC's avatar
      dshawSLDC
      Brass Contributor
      Jun 20, 2021

      SergeiBaklan 

      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's avatar
        SergeiBaklan
        MVP
        Jun 21, 2021

        dshawSLDC 

        From my point of view that's bit overcomplicated, same result gives

        =LET(
array, FILTER(TableHLP,TableHLP[District]=$B$1),
k,     SEQUENCE(ROWS(array)),
minK, 1, maxK, 30,
col,   {1,5,6,0,4,0,7,8},
res,   IF( (k>=minK)*(k<=maxK),
       IF(col=0, "", INDEX(array,k,col)),
       "remove"),
FILTER(res, INDEX(res, 0, 1) <> "remove"))

        please check in attached file.

         

        Not sure right now how to empty repeated names, will play with it some later.

Share