Find First Instance of a Value after the Last Instance of Another Value in a Row

[jurgensen]

Hi All,

I'm responsible for reviewing a long checklist of items for project deliverables at my job. Each time I perform a check, I add a new set of a columns to record my feedback. Pass status is in every other column starting as S for as many checks as it takes to pass all the items. Right now, I'm manually entering completion dates of checks, but there are a lot of items and I want to cut down on the time it takes to do housekeeping work, so I want my date completed field (column R), to be populated automatically with a formula.

The formula for Date Completed needs to show the first pass date (response is "Yes") after the last not passed or N/A date (Response is "No" or "N/A".) If the last pass status in the row is "N/A", then the date should show "N/A". If the last response in the row is "No", then the date should show as "".

Here are some additional details that may help:
1. Sometimes check statuses change over the course of the check as deliverables are reworked and revised, so the pass status doesn't always go from No --> Yes
2. The only valid responses in the "Passed?" columns are Yes, No, N/A and ""
3. There are no other fields for the checklist after column R aside from the check status and comment columns
4. The checks can go out an infinite number of times (but the most number of checks performed may be around 20)

Thank you so much to everyone for their help!!!!!!
[TABLE="class: grid, width: 600"]
<TBODY>[TR]
[TD][/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[TD]W[/TD]
[TD]X[/TD]
[TD]Y[/TD]
[TD]Z[/TD]
[TD]AA[/TD]
[TD]AB[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/1/2013[/TD]
[TD][/TD]
[TD]2/1/2013[/TD]
[TD][/TD]
[TD]3/1/2013[/TD]
[TD][/TD]
[TD]4/1/2013[/TD]
[TD][/TD]
[TD]5/1/2013[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Date Completed[/TD]
[TD]Passed?[/TD]
[TD]Comment[/TD]
[TD]Passed?[/TD]
[TD]Comment[/TD]
[TD]Passed?[/TD]
[TD]Comment[/TD]
[TD]Passed?[/TD]
[TD]Comment[/TD]
[TD]Passed?[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Item 1[/TD]
[TD]4/1/2013[/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]No[/TD]
[TD][/TD]
[TD]N/A[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

 

[jurgensen]

If that whole query is too much, then maybe just a formula that would give me the minimum date for "Yes" that is greater than the maximum date for "No" or "N/A".

 

[Dryver14]

Try this on a copy of your workbook first.

I have gone out 35 columns from the start and down 47 rows

Sub MoveData()
Dim i As Long
Dim x As Long
    For x = 54 To 19 Step -1
        For i = 3 To 50
            If Cells(i, 18) <> "" Then
                Select Case Cells(i, x).Value
                        Case Is = "yes"
                            Cells(i, 19).Value = Cells(1, x).Value
                        Case Is = "N/A"
                            Cells(i, 19).Value = "N/A"
               End Select
            End If
         Next i
    Next x
End Sub

 

[Dryver14]

not showing my previous input.

 

[Aladin Akyurek]

Hi All,

I'm responsible for reviewing a long checklist of items for project deliverables at my job. Each time I perform a check, I add a new set of a columns to record my feedback. Pass status is in every other column starting as S for as many checks as it takes to pass all the items. Right now, I'm manually entering completion dates of checks, but there are a lot of items and I want to cut down on the time it takes to do housekeeping work, so I want my date completed field (column R), to be populated automatically with a formula.

The formula for Date Completed needs to show the first pass date (response is "Yes") after the last not passed or N/A date (Response is "No" or "N/A".) If the last pass status in the row is "N/A", then the date should show "N/A". If the last response in the row is "No", then the date should show as "".

Here are some additional details that may help:
1. Sometimes check statuses change over the course of the check as deliverables are reworked and revised, so the pass status doesn't always go from No --> Yes
2. The only valid responses in the "Passed?" columns are Yes, No, N/A and ""
3. There are no other fields for the checklist after column R aside from the check status and comment columns
4. The checks can go out an infinite number of times (but the most number of checks performed may be around 20)

Thank you so much to everyone for their help!!!!!!
[TABLE="class: grid, width: 600"]
<tbody>[TR]
[TD][/TD]
[TD]Q
[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[TD]X
[/TD]
[TD]Y
[/TD]
[TD]Z
[/TD]
[TD]AA
[/TD]
[TD]AB
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/1/2013
[/TD]
[TD][/TD]
[TD]2/1/2013
[/TD]
[TD][/TD]
[TD]3/1/2013
[/TD]
[TD][/TD]
[TD]4/1/2013
[/TD]
[TD][/TD]
[TD]5/1/2013
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD][/TD]
[TD]Date Completed
[/TD]
[TD]Passed?
[/TD]
[TD]Comment
[/TD]
[TD]Passed?
[/TD]
[TD]Comment
[/TD]
[TD]Passed?
[/TD]
[TD]Comment
[/TD]
[TD]Passed?
[/TD]
[TD]Comment
[/TD]
[TD]Passed?
[/TD]
[TD]Comment
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Item 1
[/TD]
[TD]4/1/2013
[/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD]No
[/TD]
[TD][/TD]
[TD]N/A
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[TD]Yes
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

R3, control+shift+enter, not just enter:

=IFERROR(1/(1/MIN(IF($S$3:$AB$3="Yes",
  IF($S$1:$AB$1>LOOKUP(9.99999999999999E+307,
  1/MATCH($S$3:$AB$3,{"N/A","no"},0),$S$1:$AB$1),$S$1:$AB$1)))),
  SUBSTITUTE(LOOKUP(REPT("z",255),$S$3:$AB$3),"No",""))

 

[jurgensen]

Hi Aladin,

Thanks very much for your help. I tried the formula and it works great, except in two cases:
1) Sometimes checks can occur on the same day, so for example, W1 and Y1 in my original chart may sometimes both be equal to the same date.
2) There is usually text in the comment fields, but this text will never equal "Yes", "No" or "N/A". When that happens, however, it breaks the formula and the date completed in R3 shows the adjacent text.

One thing I thought of is that even though the dates can sometimes overlap, the actual position from left to right (e.g. in range S3:AB3 S3 = position 1, U3 = Position 3, etc.) on the worksheet always links to order and timing of checks performed. So what if we used this logic?
1) If the row does not contain "Yes", "No" or "N/A", then R3 = ""
2) If there is only 1 N/A, then R3 = N/A
If there is only 1 No, then R3 = ""
If there is only 1 Yes, then R3 = S1 date w/ first yes
3) If the position of the last N/A > position of the last No or N/A, then R3 = N/A
If the position of the last No > position of the last Yes or N/A, then R3 = ""
If the position of the last Yes > position of the last No or N/A, then R3 = date in S1:AB1 (min position of Yes that is also greater than the max position of the last No or N/A)