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).
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
=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.
The formulas are in cells B18 and B19 and filled across ranges B18:F18 and B19:F19.