Forum Discussion
Excel Spill Row Split
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"))
I patched startPos, i, emptyPos with IF(distinctrows=1, ...
Please check at very bottom in attached file
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)- lol Ok thanks for all your help.
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 SubSub 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 SubdshawSLDC , you are welcome.
Macros - nope, sorry. I didn't touch VBA for about 10 years and have no desire to return to it.
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.
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.