Solved: Formula to return latest date

[JimS]

I need to capture the latest date from Column-A if Column-B = Scheduled and Column-C = USA.

Where:
Column-A is various Dates
Column-B is different Statuses
Column-C is different Countries

Anyone have any ideas?

Thanks...

JimS

[RonMcK]

JimS,

How often do you need to do this drill, once or every 'n' [days|weeks|months]? Might you need to do this for different statuses and/or countries?

You might use something like this code to select out the candidates and then sort them by date to get the most recent instance.

[vba]Option Explicit
Sub find_most_recent()
Dim wb As Workbook, ws As Worksheet
Dim DateCol As String, StatusCol As String, CountryCol As String
Dim TestStatus As String, TestCountry As String, LastRow As String, rng As Range, rng2 As Range

' insert whatever code you want to use to open your file
' or make sure it's already open before you execute your code

wb = Workbooks("Your Workbook Name.xls") '
ws = wb.Worksheets("Your Worksheet") ' e.g. Sheet1 or whatever you call it
' For purpose of this illustration, I'm assuming your 3 critical data columns are A, B, and C.
' For our purposes, I'm also assuming that Row 1 is used for column titles with data starting on Row 2.
DateCol = "A"
StatusCol = "B"
CountryCol = "C"
' tempCol = "D" ' inserted by program and removed after work is done
TestStatus = "SCHEDULED"
TestCountry = "USA"

With YourWS
LastRow = .UsedRange.Rows.Count
LastCol = .UsedRange.Columns.Count
Set rng2 = .Range("A2").reaize(LastRow, LastCol)

' Sort your worksheet with the newest data either at the top or bottom, your choice.
' You can omit this step if your data is already in date order!

rng2.Sort Field:=1, Order1:=xlAscending, SortMethod:=xlPinYin, DataOption1:=xlSortNormal

' Add temp col and logic for autofilter
.Columns("D").Insert
.Range("D1").Value = "Temp"
Set rng = .Range("D2").Resize(LastRow - 1) 'a range that is just the data in col D.
rng.FormulaR1C1 = "=AND(UCASE(RC[-2])=" & TestStatus & ",UCASE(RC[-1])=" & TestCountry & ")" ' generalize using variable names
Set rng = .Range("D1").Resize(LastInfoRow)
rng.AutoFilter Field:=1, Criteria1:="TRUE"
On Error Resume Next
Set rng = rng.SpecialCells(xlCellTypeVisible) 'this causes only the selected rows ("TRUE" in col D) to be visible

' Add code here to do whatever you need to do with the most recent instance of this status code in the selected country.

On Error GoTo 0
' When you're done, delete the column the program added and all of youir data will resume being visible
.Columns("D").Delete
EndWith
End Sub
[/vba]

This should give you some ideas for solving our problem. Ask more questions. Some of the gurus can help us sort this out with you.

Cheers,

[macropod]

For a formula-based solution, you can use:
=MAX(IF((B1:B1000="Scheduled")*(C1:C1000="USA"),A1:A1000,))
as an Array formula (ie Ctrl-Shift-Enter). Adjust the ranges to suit.

[JimS]

Thank you both. I've tested the Formula and it does want I need. I'll try the VBA as I might need it as well.
Thanks again...