Find earliest date combined with status

[Ixtreme]

In column A I have several actions listed. In column B, I have for
some of those actions some due dates while in column C I have a status
field.
Something like this:

ColumnA ColumnB ColumnC
---------------------------------------------------
Action A 14-08-2007 done
Action B 26-08-2007 Pending
Action K
Action Z 29-08-2007 Awaiting

the data is not sorted.

What I would like is to find the earliest date in column B where
status is not equal to a predefined range of items)
The result should be the action to be done, it's status and the number
of days plus or minus compared to today's date.

Rng = "Open", "Pending", "Awaiting"

Result: Action B, Pending, 2 days

Thanks!!

 

[T. Valko]

It's not real clear what you want. My interpretation is:

Find the earliest date where the status is not "done" and return the
corresponding action, date and status.

If that's what you want try these formulas:

Try this array formula** for the "action" entered in cell E2:

=INDEX(A2:A5,MATCH(MIN(IF(C2:C5<>"done",IF(ISNUMBER(B2:B5),B2:B5))),B2:B5,0))

This formula entered in F2 and copied across to G2 for the date and status:

=VLOOKUP($E2,$A2:$C5,COLUMNS($A:B),0)

Format the date cell as DATE

This formula entered in cell H2 for the difference in dates based on today:

=TODAY()-F2+1

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Guest]

I'm not sure I got it exactly right. Your instructions didn't match your
results. You said you wanted "status is not equal" but your results were
"status is equal". The code below implimented "status is equal".

Call the function as follows:
=earliestaction("Open", "Pending", "Awaiting")

You can pass any number of arguments to the function.

Function EarliestAction(ParamArray actions() As Variant) As String
LastRow = Cells(Rows.Count, "B").End(xlUp).Row

First = True
EarlistRow = 0
Found = False
For RowCount = 1 To LastRow
For action = 0 To UBound(actions())
If Cells(RowCount, "C") = actions(action) Then
If First = True Then
EarlistRow = RowCount
Found = True
First = False
Exit For
Else
If Cells(RowCount, "B") < _
Cells(EarlistRow, "B") Then

EarlistRow = RowCount
Exit For
End If
End If

End If
Next action
Next RowCount
If Found = True Then
If Cells(EarlistRow, "B") > Now() Then
days = Int(Cells(EarlistRow, "B") - Now())
Else
days = Int(Now() - Cells(EarlistRow, "B"))
End If
EarliestAction = Cells(EarlistRow, "A") & _
", " & Cells(EarlistRow, "C") & ", " & _
days
If days = 1 Then
EarliestAction = EarliestAction & " day"
Else
EarliestAction = EarliestAction & " days"
End If

Else
EarliestAction = ""
End If

End Function