Forum Discussion
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 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,
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"))
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 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.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.
