ToolBox -

The trick here is to use a combination of TSCount and TSFind to locate the last square footage change.  The TSFind function has an optional parameter for the “offset”, which is a number of records to skip over.  Zero is the default, and it means “don’t skip over any records”.  If you have three records, then you need to skip over two of them to get the last record.  In general, you skip over Count-1 records to find the last record.

Since the square footage change records are stored in chronological order, the last record has the most recent effective date.  That’s the record you’ll need, so the above technique can be used.

For example, suppose that column B contains “Property” and column C contains “Primary Unit”.  You could paste the following function into column D to count the changes made to the total square footage of the unit:

·         =TSCount(“PROPERTY_PMP_RECORD_19″,”((UFPROP = ‘” & $B2 & “‘) AND (UFUNIT = ‘” & $C2 & “‘) AND (UFSFTYP = ‘Total’))”)

Then you could paste the following function into column E, which uses the TSCount  in column D (minus one) to locate the total square feet:

·         =TSFind(“PROPERTY_PMP_RECORD_19″, “UFDATE”,”((UFPROP = ‘” & $B2 & “‘) AND (UFUNIT = ‘” & $C2 & “‘) AND (UFSFTYP = ‘Total’))”, $D2-1)