Forum Discussion
Excel Spill Row Split
- Jun 19, 2021
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 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.
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)
- dshawSLDCJun 25, 2021Brass Contributorlol Ok thanks for all your help.
- dshawSLDCJun 25, 2021Brass Contributor
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
- SergeiBaklanJun 25, 2021MVP
dshawSLDC , you are welcome.
Macros - nope, sorry. I didn't touch VBA for about 10 years and have no desire to return to it.
- dshawSLDCJun 25, 2021Brass Contributor
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.
- SergeiBaklanJun 25, 2021MVP
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.
- dshawSLDCJun 25, 2021Brass Contributor
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)
- dshawSLDCJun 24, 2021Brass Contributor
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.
- SergeiBaklanJun 23, 2021MVP
I patched startPos, i, emptyPos with IF(distinctrows=1, ...
Please check at very bottom in attached file
- dshawSLDCJun 22, 2021Brass ContributorYeah 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.
- SergeiBaklanJun 22, 2021MVP
Perhaps to wrap by IFERROR(), but don't know what exactly. If you don't solve will try to play tomorrow.
- dshawSLDCJun 22, 2021Brass Contributor
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?
- SergeiBaklanJun 22, 2021MVP
dshawSLDC , you are welcome, that was interesting task
- dshawSLDCJun 22, 2021Brass ContributorThanks, Again I am very appreciative of your time and willingness to help.
- SergeiBaklanJun 22, 2021MVP
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.