SOLVED

Massive lease schedule workbook returning lease starting and termination dates

Copper Contributor

Hi everyone! 

 

I hope you are well :) 

 

I am an auditor and was wondering if someone could please help me solve the following problem that Im facing when dealing with my clients lease schedules:

 

The layout of the lease schedule is that each lease is put in a workbook that follows horizontally one after the other. The related payments and month of each payment, then follows down the rows. I have included a screenshot of the schedules to this post (made up by myself as I obviously cannot post any client information here). 

 

AuditorSB_0-1679048278282.png

 

The client has hundreds of leases in place, but for the purpose of this example I have shown only two examples. You will see that the months where the lease is in place is indicated by the months that have a payment amount. 

 

What I need to do is figure out a way to get Excel to return the starting and terminating date of the lease in the schedule. In the above examples, I would want Excel to return the following: 

 

  • Lease #1: Start = October 2022 and Terminate = February 2023 
  • Lease #2: Start = July 2022 and Terminate = November 2022 

 

In addition, since this will be applied on hundreds of leases - a way to do this across the population would be greatly appreciated!!! (I do have basic macro knowledge, but it might even be easier to past the data transposed and work with vlookups/ if formulas somehow?) 

 

Thank you so much in advance! 

 

Kind regards 

 

Struggling Auditor drowning in busy season :) 

 

 

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@AuditorSB 

=INDEX($A$3:$A$16,SMALL(IF(B$3:B$16<>"",ROW($B$3:$B$16)-2),1))

You can try this formula for the first lease. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

=INDEX($A$3:$A$16,LARGE(IF(B$3:B$16<>"",ROW($B$3:$B$16)-2),1))

This is the formula for the last lease in the example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

lease.JPG

The formulas are in cells B18 and B19 and filled across ranges B18:F18 and B19:F19.

@OliverScheurich WOW! Thank you so much for your assistance! This initially worked and I am applying it to the whole population now and hopefully it will work there as well :) 

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@AuditorSB 

=INDEX($A$3:$A$16,SMALL(IF(B$3:B$16<>"",ROW($B$3:$B$16)-2),1))

You can try this formula for the first lease. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

=INDEX($A$3:$A$16,LARGE(IF(B$3:B$16<>"",ROW($B$3:$B$16)-2),1))

This is the formula for the last lease in the example. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

lease.JPG

The formulas are in cells B18 and B19 and filled across ranges B18:F18 and B19:F19.

View solution in original post