SOLVED

Excel Spill Row Split

Brass Contributor

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,

 

 

28 Replies
Yeah I thought about that but the weird thing is it does it also if the district you pull only has 1 name. I could understand if it was 0 names. so im not sure what to do. I will continue to play with it. Ill let you know if i find the solution.

@dshawSLDC 

I patched startPos, i, emptyPos with IF(distinctrows=1, ...

Please check at very bottom in attached file

@Sergei Baklan 

So I put that code in and this is what its doing.

Page 1.								
	Name		Amount		Rate		Service Total	Name Total
	Tom Davidson	.Main Introduction	12		$167.00		$2,004.00	
	Tom Davidson	.Main Introduction	12		$167.00		$2,004.00	
		Introduction To Hello World	90		$109.00		$163.50	
								
								

I attached the example also. Im not sure why its doing this.

@Sergei Baklan 

I think i did it. I used this as my work around. There is probably a better solution but this works for now.

 

=LET(
columnsToShow,    {1,5,6,0,4,0,7,8},
columnNameTotal, 8,
lmaxs,1,
mins, 1, maxs,6,
selectedDistrict, $A$7,

array,      SORT(FILTER(TableHLP,TableHLP[District]=selectedDistrict)),
districts,SORT(FILTER(
                         FILTER(TableHLP,TableHLP[District]=selectedDistrict),
                         {1,0,0,0,0,0,0,0})),
suc,           UNIQUE(districts),
af,             MATCH(districts,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),
namecount,  ROWS(suc),
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, IF( distinctRows=1, 1, 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, IF(distinctRows=1, SEQUENCE(arrayRows,,0,0), 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( columnsToShow=0, "",
             IF( (vPrev=v)*(columnsToShow=1)*(i<>1), "",
             IF( i < arrayRows,
                 IF( (vNext=v)*(columnsToShow=columnNameTotal), "",  v),
                 v)
              ) )),

singlerow,       ROWS(array),
rowseq,       SEQUENCE(singlerow+2,,0,1),

minsingle,IF(lmaxs=1,mins,cm+1), maxsingle, MIN(c, singlerow),

arrayrowseq,       INDEX(array,rowseq,columnsToShow),
xPrev,   INDEX(array,rowseq-1,columnsToShow),
xNext,   INDEX(array,rowseq+1,columnsToShow),
ressingle,    IF( (rowseq>=minsingle)*(rowseq<=maxsingle),
               IF(columnsToShow=0, "",
               IF( (xPrev=arrayrowseq)*(columnsToShow=1)* (rowseq<>minsingle),
                     "",
                    IF( rowseq<maxsingle,
                    IF( (xNext=arrayrowseq)*(columnsToShow=8), "",  arrayrowseq ),
                    arrayrowseq )
                )   ),
               "remove"),
Singleres,FILTER(ressingle, (INDEX(ressingle, 0, 1) <> "remove")+(INDEX(ressingle, 0, 1) = "Name")),
Final,IFERROR(IF(namecount<>1,res,Singleres),"None Available"),
Final)

@dshawSLDC 

Sorry, I missed your post yesterday.

It works if we change

cntseq, SEQUENCE(arrayR,,0,1),

on

cntseq, SEQUENCE(arrayR,,1,1),

 

Zero was from previous variant when we included headers into output array. Perhaps with this the rest also could be simplified.

Please check in attached.

@Sergei Baklan 

Yeah that did it. Much more elegant than my solution. LOL.  Have you ever messed with macros? I'm working on the same file trying to create a print button that will cycle thru each district and print the sheet if there are names returned. So far I have the buttons created and can print the single district. But I'm struggling on the code to make it cycle thru the district list. I attached the example If this is something you are able to assist me in. or pointing me to the right direction. Thanks again for your help.

@dshawSLDC , you are welcome.

Macros - nope, sorry. I didn't touch VBA for about 10 years and have no desire to return to it.

@Sergei Baklan 

So just in case this is what I have so far. I was able to get it to print all but it also prints the ones that are blank. is there away to tell it if it does not have data to not print that page? 

 

Sub PrintAllDistricts()
    Dim xRg As Range
    Dim xCell As Range
    Dim xRgVList As Range
    Set xRg = Worksheets("Report Select").Range("B1")
    Set xRgVList = Evaluate(xRg.Validation.Formula1)
    For Each xCell In xRgVList
        xRg = xCell.Value
        Call Module1.PrintDistrict
    Next
End Sub

 

Sub PrintDistrict()
Sheets(Array("Page1", "Page2")).Select
Sheets("Page1").Activate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets(Array("Page1", "Page2")).Select
Sheets("Report Select").Activate
Range("B1").Select
End Sub

 

lol Ok thanks for all your help.