Excel Spill Row Split

[dshawSLDC]

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 services that looks like something like this.

Name        Services

John            ABC

John            DEF

John            GHI

greg             DEF

greg             GHI

cindy            ABC

Cindy            GHI

There is about 100 Records. I would like to be able to split this Data 30 records on sheet1, 30 on sheet2 and 30 on sheet3. Is there a way to split the spill by providing a minimum and maximum range? Second question is there away to add a Space after each person. and remove duplicate names?

Example:

Name        Services

John            ABC

                    DEF

                    GHI

greg             DEF

                    GHI

cindy            ABC

                     GHI

I have tried a bunch of different things and have hit a wall. Any help would be greatly appreciated.

I am open to VBA or macros if the solution needs to go there.

Thanks,

[#2 · Accepted solution]

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

[#3]

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.

[#4]

@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.

[#5]

@Sergei Baklan 

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)

[#6]

@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.

[#7]

@dshawSLDC 

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

[#8]

@Sergei Baklan 

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.

[#9]

@dshawSLDC 

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

[#10]

@Sergei Baklan 

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.

[#11]

@dshawSLDC 

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 )

[#12]

@Sergei Baklan 

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)

[#13]

@Sergei Baklan 

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. 

[#14]

@dshawSLDC 

I Think I got it. Check out the Formula and let me know what you think.

=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, 8,
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, 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)

[#15]

@Sergei Baklan 

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)

[#16]

@dshawSLDC 

Looks great. Perhaps the code could be bit optimized, but that's on another iterations. What I'd do now - apply at least minimum of formatting to make function easier to read, and use more descriptive name. Perhaps except indexes. Otherwise in few month you forget what is what and it takes more time to analyze the code.

Comments here if can be used when indirectly. I'm not fun of comments, if use very few of them is enough and they shall answer on question why we do that, not what we do here. Descriptive names is enough for that.

Quite small cosmetic changes are in attached file, spill at very bottom.

[#17]

Thanks, Again I am very appreciative of your time and willingness to help.

[#18]

@dshawSLDC , you are welcome, that was interesting task

[#19]

@Sergei Baklan 

Hey I found an error. If you only have 1 name in a district you get a #CALC! error " empty arrays are not supported". any thoughts on how to fix this?

[#20]

@dshawSLDC 

Perhaps to wrap by IFERROR(), but don't know what exactly. If you don't solve will try to play tomorrow.