Originally Posted by
Aflatoon
You could also do something like this:
=LET(a,IFERROR(SORT(FILTER(FILTER(BusMapData,(E1<=MyTimeIn)*(E2>=MyTimeOut) *(MyCancel=0),""),{1,1,0,1,1,0}),4),""),b,FILTER(MyShopData,(E1<=MyBackfrmS hop)*(E2>=MyOut2Shop),""),IF(a="",b,IF(b="",a,VSTACK(a,b))))
Aflatoon, I hope this quote will somehow send a message to you, as this is an older thread marked solved. I made some changes to how I ordered columns which affected formulas on the sheet that pulls the formulas. But learning forces me to find new and easier ways, and I like the simplicity of the quoted formula. Even so you won't find the LET function in my next question which is a result of evolutionary changes to my original idea.
As a reminder it is an effort to stack a filter from BkgTbl with a filter from OOSShopData for dates that fall within a range of time. Both are short a column, which either states that it came from Trip (BkgTbl) or Shop - thus the Expands. But the Shop is short another column, so I tried expanding a blank column 5. Anyway, the stacked formula works well, except that it again (because of the stack I think) accepts a blank row when nothing exists in either side of the stack. My attempts to use your LET function came up with error "You can't include a parameter to a LET function after defining its calculation."
So I created the VSTACK Spill area in a cell way over to the right and named that cell "MFTempVStack"
I then created the second formula where it is needed and basically called on it to filter blank items from column 1 of the spill area. It worked.
My question is ...
Is there any way to put the following two formulae into one?
=SORT(VSTACK(EXPAND(FILTER(INDEX(BkgTbl,SEQUENCE(ROWS(BkgTbl)),{12,13,14,15,10}),(E1<BkgTIn)*(E2>=BkgTOut)*(BkgCancel=0),""),,6,"Trip"),(EXPAND(EXPAND(FILTER(INDEX(OOSShopData,SEQUENCE(ROWS(OOSShopData)),{3,4,1,2}),(E1<OOSBackfrmShop)*(E2>=OOSOut2Shop),""),,5,""),,6,"Shop"))),3)
=FILTER(MFTempVStack#,CHOOSECOLS(MFTempVStack#,1)<>"")
Does keeping them separate caused any difficulty? The entire MF - Map Filter sheet is background only anyway and never seen by the user.
Thanks
Gary