Lookup expiration dates by calendar month

[Ken King]

I have a worksheet tab with the following three columns sorted by Expire Date
with over 500 rows of data.
A B C
Expire Suite #
Date Occupant RSF
3/31/2009 105: Name A 100
1/10/2010 210: Name B 200
2/15/2011 107: Name C 150

I have a 10 year calendar on another worksheet tab (example below) for
presenting expirations by month/year with Suite #: Occupant and RSF (rentable
square feet).

A B C D E
F...
1/1/2009 2/1/2009 3/1/2009… 12/1/2009
105: Name A 100

1/2/2010 2/1/2010 3/1/2010… 12/1/2010
210: Name B 200


1/1/2011 2/1/2011 3/1/2011… 12/1/2011
107: Name C 150

I am trying to write a formula or function to find expiration dates each
calendar month for the 10-year period and return the ‘Suite #: Occupant’ in
the column below the date and ‘RSF’ in the adjacent column. Is it possible
to do a lookup for a calendar date i.e. 1/1/2009 to 1/31/2009 etc. and return
the corresponding 'Suite #: Occupant' and 'RSF' columns?
Thanks,
Ken

 

[N harkawat]

You could easily get this info using a Pivot table. Once you have the dates
right click on a date on the Pivot table and select "group and show
detail"--> group and select month+Year
Google it if you are unsure how to create a Pivot table

 

[Ken King]

You could easily get this info using a Pivot table. Once you have the dates
right click on a date on the Pivot table and select "group and show
detail"--> group and select month+Year
Google it if you are unsure how to create a Pivot table

I don't think pivot table will work since I want a detailed report of all my
data by each calendar month for 10 years.

 

[Shane Devenshire]

Hi,

How are you going to handle split rentals - JJ's rental goes through 1/15/09
and then Q takes it over on 1/16/09?

 

[Shane Devenshire]

Hi,

Think I need to add a second question, for any one month with two different
properties leases expiring how do you show them. Its starting to look like a
macro will be you only solution unless only one suite/occpt. can occur in an
one month/year.

In that case try a setup like this, where this is the range D1:H6. (you
would extend it to the right to cover all 12 months.

1/1/2009 2/1/2009 3/1/2009 4/1/2009
1/1/2009 105:NameA 100
1/1/2010 210:NameB 200
1/1/2011 107:NameC 150
1/1/2012
1/1/2013

The formulas starting in E2 are

=IF(SUMPRODUCT(--(YEAR($D2)=YEAR($A$2:$A$4)),--(MONTH(E$1)=MONTH($A$2:$A$4)),ROW($A$2:$A$4)-1)<>0,INDEX($B$2:$B$4,SUMPRODUCT(--(YEAR($D2)=YEAR($A$2:$A$4)),--(MONTH(E$1)=MONTH($A$2:$A$4)),ROW($A$2:$A$4)-1))&"
"&INDEX($C$2:$C$4,SUMPRODUCT(--(YEAR($D2)=YEAR($A$2:$A$4)),--(MONTH(E$1)=MONTH($A$2:$A$4)),ROW($A$2:$A$4)-1)),"")