Compare a data set to a range of criteria and if the criteria are met, output relevant data



I’ve been struggling with this for a while now, maybe someone out there can help?

I have a tenancy schedule with data relating to tenants i.e name of tenant, lease start date, lease end date and rental.

I want to pick out all the tenants with lease start dates that fall within a date range that i will specify. For example my range is Jan 2010 to June 2011. I want to pick out all the tenants that have lease start dates falling within that range and output their “name”, “lease start date”, “lease end date” and “rental” into a separate worksheet. So basically copy the whole row into another worksheet if the lease start date falls within Jan 2010 – June 2010.

I have tried vlookups but that doesnt work if more than one lease has the same start date and falls within Jan 2010-June 2010. HAve also tried if statements but then i end up with a million nested ifs and it leaves spaces where tenant lease dates dont fall within my range.


Can anyone help? 🙁

5 thoughts on “Compare a data set to a range of criteria and if the criteria are met, output relevant data

  1. Excel Geek

    Hello there, try this macro code below…

    Sub Tenancy()

    Application.ScreenUpdating = False

    Dim NextRow As Long
    Dim Date1, Date2 As Date

    ' Clears the results from the previous date search


    Date1 = Cells(1, 6)
    Date2 = Cells(1, 7)

    For X = 2 To 1000 Step 1

    If Cells(X, 2) = "" Then Exit For
    'Checks the dates and copies rows that match
    If Cells(X, 2) >= Date1 And Cells(X, 2) < = Date2 Then
    If Cells(X, 3) >= Date1 And Cells(X, 3) < = Date2 Then
    Cells(X, 2).EntireRow.Copy

    NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
    Cells(NextRow, 1).Select
    End If
    End If

    Next X

    Sheets("Sheet1").Cells(1, 1).Select
    Application.ScreenUpdating = True
    End Sub

    I have also attached the workbook to this comment so you can download it. Simply replace the start and end dates with your chosen dates and click the search button.

  2. Rubia

    Thanks so much, I downloaded the workbook but everytime I try to run the ‘search’ I get a “type mismatch” error and when i try to debug it, it points to the part ‘Date2 = Cells(1, 7)’.

  3. Excel Geek

    Hello there

    Cells(1,6) refers to cell F1
    Cells(1,7) refers to cell G1

    You need to enter your date parameters into these to cells. For example, 01/09/2009 into cell H1 and the end date 31/12/2010 into cell G1.

    The dates need to be entered dd/mm/yyyy.

    Please let me know if the above helps, or if it is another error.


Leave a Reply

Your email address will not be published. Required fields are marked *