Pulling "Open" slots from a table into a separate tab

[adji1530]

Hi, I'm looking to translate the following table to a separate tab/list showing only Open Times

So a sample output of this new tab/list would be: 

Time Slot             |  Seat | Status

1:00PM - 3:30PM |    A   | Open

Any ideas? Thanks in advance for the help!!

[#2]

@adji1530 

Look into the FILTER function as described in this YouTube video. https://www.youtube.com/watch?v=9I9DtFOVPIg

If you need more help, could I ask you to post a copy of that actual spreadsheet, rather than just an image, 

It's also possible that Power Query could work, if you're not on a Mac.

[#3]

@mathetes 

Thanks for the response! Attached spreadsheet found in tab "Table". I'm fiddling with the "Filter" function as well but am not having much success.

In the "Output" tab ideally, the "Seat" and "Time Slots" column should be dynamic showing "Open" slots as the table updates based off of reservations coming into the "Input" tab.

I've linked the "Input Data" tab already to the "Table" in my own spreadsheet so the table changes based off of what is in the "Input" data but only pasted the values over instead of the entire formula in the attached just for demonstrative purposes.

Thanks again for the help and please let me know if any clarity I can provide.

[#4]

@adji1530 

I'm not sure how you want the results displayed, but here are two ways that might be helpful. The second way uses FILTER (as a demonstration of how it might work). I think the other is actually more helpful, and it just uses the tried and true VLOOKUP function.

They will change (or should, at any rate) on a dynamic basis when you update the input, since you say that affects the Table tab. I haven't verified that.

[#5]

@mathetes 

Thank you, this is so helpful! I do wonder if it's possible to translate the output into list form like this? Not sure if you know a way to do that.

[#6]

I'm sure it's possible, but nothing pops to mind immediately.

If you're populating the "Table" data already, based on changes to "InputData," I should think you'd be able to puzzle that through as well. I just don't have the time at the moment, but it'll be percolating in the back of my mind for the rest of the day.

It would help to know if there's a minimum and maximum to the magnitude of reservations you accept. The two examples are quite different, I note. Your basic table of times has a granularity (if one can think of it that way) of 15 minutes, quarter hours. Table reservations would routinely run at least 60-90-120. If one could make some specific assumption about that sort of thing, it would get easier.....

That said, assuming you're looking for blocks of time that are open, I would think that this display you already have would be very functional.

[#7]

Gotcha, thank you, any help you can provide would be really appreciated! Yes. There's currently a fixed reservation length of the following:

Normal (Marked as FT):
60/90/120/150 minutes with 15 minutes of set-up and 15 minutes of bussing

Events (Marked as AO):
120/150/180 minutes with 30 minutes of set-up and 15 minutes of bussing

It's only in 15 minute time periods to properly block of bussing and times.

[#8]

@mathetes 

Gotcha, thank you! Any help would be appreciated if you have the time.

There is actually a min and max length with a couple differences in between.

Normal Reservations (Labeled "FT"):

60/90/120/150 minutes with 15-minutes set-up and 15-minutes bussing

Event Reservations (Labeled "AO"):

120/150/180 minutes with 30-minutes set-up and 15-minutes bussing

The only reason why it is broken out into 15-minute time periods is to account for the 15-minute bussing blocked off!

[#9]

@mathetes 

For some reason my reply isn't showing up. But thank you for all your help!

I actually do have fixed times:

Normal Reservations (Labeled "FT") are 60/90/120/150 minutes long with 15-minutes set-up and 15-minutes bussing

Event Reservations (Labeled "AO") are 120/150/180 minutes long with 30-minutes set-up and 15-minutes bussing.

Any guidance you would have on creating a list like the one mentioned above would be greatly appreciated!