Make VSTACK(FilterFunctionA,FilterFunctionB) show parent

[garyj]

Hello...

I don't know if the title makes sense or not, but here is my goal. I have a dispatch sheet which I filter to find the buses which are on the road. It looks for:

Coach#, Driver, DepartDateTime, ReturnDateTime

I VSTACK that filter with another that looks for buses that are in the shop. It looks for:

Coach#, Reason, StartDateTime, EndDateTime

Together these two together give all the Coaches that are out. The second column gives either the driver or reason the coach is in the shop. The third and fourth columns show the datetimes for these, and I use that to provide for a calendar view of dates when buses are not available for one of the two reasons (already on the road, and in the shop).

It works great, thanks to help from this forum in past questions.

The problem I just encountered is that I now have a reason for which to recall which data comes from which stack. I would like to add a field to the group that says either road or shop. But how to do it. I have floundered at ways to do so within the main formula, which is complicated, as follows:

==LET(x,VSTACK(SORT(FILTER(FILTER(BkgBusMapData,(E1<=BkgTIn)*(E2>=BkgTOut)*(BkgCancel=0),{"¬","","","","",""}),{1,1,0,1,1,0}),4),FILTER(OOSShopData,(E1<=OOSBackfrmShop)*(E2>=OOSOut2Shop),{"¬","","",""})),FILTER(x,TAKE(x,,1)<>"¬"))

I am willing to receive ideas on putting something in there... but after failing to find a resolution I finally decided on another approach. Unfortunately I need help on this as well.
My second approach was to create a column that uses some of the filtered spill data to give the needed data.

I am attaching a picture with the columns. My original, working formula, is in the first column and it returns the background colors of the buses on the busmap using the equipment table. It spills and works fine. A5 by the way can never be blank, but adding that If made the difference between the column spilling and not.

So I added the last column to compare the reasons for bus in shop to the list of available reasons: ListFShopR. Any bus on the road will have a driver, not a shop reason, in that field... so it will then evaluate as FALSE. As you can see, the 4 buses in the shop evaluate as True. That line also works, but it doesn't spill, and F5# of course doesn't work. I tried other methods to make it spill, but it makes the formula error. So I entered the appropriate formula into each row, and it works.

My final goal is to have all buses in the shop for maintenance or inspections to show on the busmap calendar as red... color 192. So the combination uses an IF formula to attach the ideas into one formula. BUT it returns the same values as the first formula. I do not understand why.



Thanks again for any help on either side, though if I had my preference, I would choose the second method to solve.

GaryVSTACK issue.jpg

[georgiboy]

If i understand correctly then you could create 2 temporary stacks of data within the formula and use HSTACK to join them to the original data, you would then use the created 2 column ranges to pass to the VSTACK.

Like the below and attached:

=LET(range1,A2:A8,range2,B2:B11,
  road,TEXTSPLIT(REPT("Road|",COUNTA(range1)),,"|",1,1),
  shop,TEXTSPLIT(REPT("Shop|",COUNTA(range2)),,"|",1,1),
VSTACK(HSTACK(road,range1),HSTACK(shop,range2)))

To apply it to your data it would be easier to see the spreadsheet, but you get the picture from the attached.

[Aflatoon]

FWIW, you could also use EXPAND there:


road,EXPAND("Road",COUNTA(range1),,"Road"),
shop,EXPAND("Shop",COUNTA(range1),,"Shop"),

[georgiboy]

Thanks @Aflatoon,

EXPAND is something i need to 'expand' my knowledge with (sigh - bad joke i know)

[Aflatoon]

Excellent pun...

[garyj]

Hey, EXPAND is a good fit there. Thanks for that.

I actually tried both ideas (@Georgiboy & @Aflatoon)... but with the effort to keep the #spill array working, and the length of that first line of code, it gets confusing. EXPAND worked well, except that after adding the text ("Road"/"Shop") as an extra column, the same problem occurred in the last column - in that it never changes the color to 192, even if the value in column E is "Shop" (as per):

IF(E5="Shop",192,VLOOKUP(A5,"Equip_tbl",11,FALSE)

So I deleted the changes and chose instead to write the 192 shop color into the code.
- BMFTotinList is the name of a cell that counts the spill total rows: =ROWS(A5#)
- BMFTotinShop is the name of a cell that uses COUNT(filter function for the second stack).
- The difference is the number of VBA iterations for getting the shop color from the chart, and then the rest get the 192. The VBA code snippet looks like this...

    With wks
        ctR = .Range("BMFTotinList")
        ctRShp = ctR - .Range("BMFTotinShop")
        For ctR = 1 To ctR
            Set iCell = wks.Range("A" & ctR + 4)
            mybus = iCell.Value
            mydrvr = iCell.Offset(0, 1).Value
            If iCell.Offset(0, 12) = False Then myleft1 = "" Else myleft1 = iCell.Offset(0, 12).Value
            If iCell.Offset(0, 13) = False Then myleft2 = "" Else myleft2 = iCell.Offset(0, 13).Value
            If iCell.Offset(0, 14) = False Then mywd1 = "" Else mywd1 = iCell.Offset(0, 14).Value
            If iCell.Offset(0, 15) = False Then mywd2 = "" Else mywd2 = iCell.Offset(0, 15).Value
            mytop1 = iCell.Offset(0, 16).Value
            mytop2 = iCell.Offset(0, 17).Value
            mybk = InShp.Interior.Color 'Index 'this is for buses in shop, reassign next if on road
            If ctRShp > 0 Then
                ctRShp = ctRShp - 1
                mybk = iCell.Offset(0, 19).Value
                End If
            If myleft1 = "" Then GoTo draw_week2
draw_week1:

This approach worked, and so I will not continue to find a resolution for my question above. What do I learn? Well I learned a bit about working VSTACK with HSTACK, and I learned how to work EXPAND. I solved the real problem - how to treat the shop buses different (visually, on the shop map). So I will mark the thread as solved, even though I didn't solve two of my workarounds.

Thanks for your help.

[Aflatoon]

The other thing you could learn is that it's quite hard for people to help if you post pictures of some of your data and formulas out of context...