Vacant Units - Multiple Entries but only showing on status for most recent entry

[SamuraiEd]

I have a rental spread sheet for a hotel which I use to input new guests etc. This sheet keeps track of all tenants since I started the record.
I am having trouble being able to list all the occupied and vacant units - vacant units being the problem.
Its not too difficult to change the status to Occupied or Vacant, but if I choose VACANT on the filter I get all the people who stayed in that unit even months back.
I'm only interested in listing all the occupied and most recent vacant units.

=IF(AND(G43<$F$9,H43<$F$9),"Vacant","Occupied")
$F$9 is TODAY()
G43 is check in date
H43 is check out date

[ChemistB]

I'd need to see a sample spreadsheet to see where your data is coming from. Your unit would be vacant if the latest check out date is > than latest check in date or if the number of check in dates = the number of check out dates. I can't help you with the information you've given. I'd need to see how your data is set up (Go Advanced> Manage Attachments)

[SamuraiEd]

Thanks for taking the time to have a look at this. I've uploaded a sample file.

[ChemistB]

Okay, I created a separate list (on a separate sheet) of the current status of all units.

First, are you sure of this formula in Col B?

Please Login or Register  to view this content.

I would think you'd want it to Occupied if person is checking in on that day but vacant if checking out on that day so should be

Please Login or Register  to view this content.

Anyway, with a list of all your units, starting in A2
in B2 copied down

Please Login or Register  to view this content.

Basically if there are any occurrences of the unit being occupied on the date in U3, then it is occupied on that date, otherwise it is vacant.
Does this work for you?

[ChemistB]

Oh, I converted all your units to Numbers (you had made those less than 100 into text so you could prefix them with a zero). I used custom number format to place the 0 in front of the units less than 100.

[SamuraiEd]

Thanks for the change in converting the text to numbers.

you are quite right on your point about my B column formula, im guilty of rushing it when creating the sample sheet.

For your formula that you put on the separate sheet, it seems to work well except that in the case (eg. 102) where it is occupied now and in the future, then it takes whichever one is listed on top.

[ChemistB]

Okay, 102 is occupied from Sept 28 to Dec 28th. Why would you not want that one listed?