Tricky Dynamic filtering problem when using VSTACK

[garyj]

Hello again..

I have a sheet that I use in the background which gathers data needed for another sheet.
The background sheet basically uses a dynamic filter as follows.


SORT(FILTER(FILTER(BusMapData,(E1<=MyTimeIn)*(E2>=MyTimeOut)*(MyCancel=0),""),{1,1,0,1,1,0}),4)

The second filter uses the ones and zeros to decide which columns will be included (the ones are included). The 4 is the row for sort.
I realized I needed to add another group of data to the same sheet, and on viewing the items, found it to be comparable, though it doesn't need sort nor any reduction of columns.


FILTER(MyShopData,(E1<=MyBackfrmShop)*(E2>=MyOut2Shop),"")

So I put them together as one using VSTACK.


=VSTACK(SORT(FILTER(FILTER(BusMapData,(E1<=MyTimeIn)*(E2>=MyTimeOut)*(MyCancel=0),""),{1,1,0,1,1,0}),4),FILTER(MyShopData,(E1<=MyBackfrmShop)*(E2>=MyOut2Shop),""))

It works fine until there is no items in the first set of filters, then returns an error even though there are items in the second group.
Any ideas what I did wrong?

Gary

[p45cal]

Not sure if this is the best answer (I think there might be a more elegant solution), but try replacing the single space in your first formula:
" "
with
{"","","","","",""}

I realise this will lead to a single blank row being returned when no row is to be returned, which will lead to needing a further filtering to eliminate, so you might use an unusual character in the first column by using say:
{"¬","","","","",""}
then filtering the VSTACKED array on the first column, perhaps:

=LET(x,VSTACK(SORT(FILTER(FILTER(BusMapData,(E1<=MyTimeIn)*(E2>=MyTimeOut)*(MyCancel=0),{"¬","","","","",""}),{1,1,0,1,1,0}),4),FILTER(MyShopData,(E1<=MyBackfrmShop)*(E2>=MyOut2Shop),{"¬","","",""})),FILTER(x,TAKE(x,,1)<>"¬"))
(here, I've treated the second array in a similar way)

[garyj]

That works p45cal, and it gave me an idea, which also works... though I think yours is less clumsy, I follow mine easier.


=IF(ISERR(FILTER(FILTER(BusMapData,(E1<=MyTimeIn)*(E2>=MyTimeOut)*(MyCancel=0),""),{1,1,0,1,1,0})),FILTER(MyShopData,(E1<=MyBackfrmShop)*(E2>=MyOut2Shop),""),VSTACK(SORT(FILTER(FILTER(BusMapData,(E1<=MyTimeIn)*(E2>=MyTimeOut)*(MyCancel=0),""),{1,1,0,1,1,0}),4),FILTER(MyShopData,(E1<=MyBackfrmShop)*(E2>=MyOut2Shop),"")))

Thanks for your help.
Gary

[garyj]

I've been trying to understand what Let(x,array,filter) is doing. I've only used let in basic Let x = y or similar declarations. I think this is what is stumping me.

[p45cal]

Probably best explained by MS themselves here: https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999

[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<=MyBackfrmShop)*(E2>=MyOut2Shop),""),IF(a="",b,IF(b="",a,VSTACK(a,b))))

[garyj]

I found a way that my formula still failed... so back to yours p45cal... understanding is growing. I appreciate your work.
I forgot to mark as solved before, but I think I will now.
Thanks

[garyj]

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<=MyBackfrmShop)*(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

[Aflatoon]

Is there any way to put the following two formulae into one?

Yes - use LET to store the result of the first formula. ;)


Does keeping them separate caused any difficulty?

No there's no real downside.