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

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

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)

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?

Formula:

=IF(AND(E9<$U$3,F9>$U$3),"Occupied","Vacant")

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

Formula:

=IF(AND(E9<=$U$3,F9>$U$3),"Occupied","Vacant")

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

Formula:

=IF(COUNTIFS('LEASING REPORT'!$C$9:$C$279, A2, 'LEASING REPORT'!$B$9:$B$279, "Occupied"),"Occupied", "Vacant")

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?

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.

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