CODEWITHSUNDEEP
excelvbaexcel-formula
Cory Britton
Cory Britton

Reputation: 5

Compare columns and output a value

Based on fields containing the Release Name and Change Date, I need to determine in what stage of that release process, the change occurred.

In the example below, the change occurred in the January Analysis stage. I need to do this a few hundred times.

You are given a Month and Date.

You will see the month, phase, and date range (Start & Calculation End). The month and date range are used to determine the phase. Therefore for January 10/10/17 falls between January 10/9/17 - 11/5/2017 which would put it in the Analysis phase.

Below is a link to an image of how the Stage is determined and spreadsheet is setup.

Phase Determination
Phase Determination

Release   Change Date
January   10/10/2017

Release   Stage                Start        Calculation End
January   Form Release         8/14/2017    9/10/2017
January   Requirements Review  9/11/2017    10/8/2017
January   Analysis             10/9/2017    11/5/2017
January   Development          11/6/2017    12/10/2017
January   Functional Test      12/11/2017   12/17/2017
January   QA                   12/18/2017   1/7/2018
January   Live                 1/7/2018     1/7/2018
February  Form Release         9/11/2017    10/8/2017
February  Requirements Review  10/9/2017    11/5/2017
February  Analysis             11/6/2017    12/10/2017
February  Development          12/11/2017   1/7/2018
February  Functional Test      1/8/2018     1/14/2018
February  QA                   1/15/2018    2/11/2018
February  Live                 2/11/2018    2/11/2018

Reference Error Reference Errore

Evaluate Formula
Evaluate Formula

Upvotes: 0

Views: 71

Answers (2)

BigBen
BigBen

Reputation: 50007

Here's a relatively straightforward way to do this, as I understand the question.

  1. Create a named range for each month - e.g. in the screenshot, "January" would correspond to A2:D8, and "February" to A9:D15.
  2. Then use INDIRECT to refer to that named range, and INDEX and MATCH to return the stage. Within your MATCH function, you will use an INDEX and INDIRECT to get the 3rd (Start) column of the range "January", in which you will try to match your date. The match_type argument (3rd argument of MATCH) is 1, meaning that it finds the largest value that is less than the lookup value. This will give you your row number. Then in your outermost INDEX formula, the column number is 2 (the Stage column).

This is the formula in H2:

=INDEX(INDIRECT(F2),MATCH(G2,INDEX(INDIRECT(F2),,3),1),2)

enter image description here

Upvotes: 0

DavidP
DavidP

Reputation: 623

You can use a VBA macro to loop through the rows in the table and look for the row that matches the criteria. Below is some VBA that will do it, you can modify this code to use cell references for the inputs, and for the range to search instead of ActiveSheet.UsedRange if you want.

Sub getStage()
    Dim release As String
    release = "January"
    Dim eventDate As Date
    eventDate = "2017-10-10"

    For Each Row In ActiveSheet.UsedRange.Rows
        If (Row.Cells(1, 1) = release And Row.Cells(1, 3) <= eventDate And Row.Cells(1, 4) >= eventDate) Then
            Debug.Print (Row.Cells(1, 2))
        End If
    Next
End Sub

Upvotes: 0

Related Questions