Lookup/Find MAX value that is also latest/most recent?

[jhallgren]

Have sheet with purchase dates in 'A' and amts in 'B'...found max amt value ok, but when duplicated, need the latest/most recent related date...how?
Thanks to other threads, found and adapted this:

=INDEX(A2:A279,MATCH(MAX(B2:B279),B2:B279,0))

For example, if amt in B150 and B270 are both = "MAX", I need the date from A270, but above MATCH gives me A150 instead.
Entries are in ascending date order, and there may also be duplicates for a given date.

Thanks!
(Excel 2000)

 

[Fazza]

Hi,

If you have field headers exactly like "PurchaseDate" & "Amt" and name the source data range - headers and data - with defined name "MD" (short for MyData), go via menu data, import external data, new database query. Excel files. OK. Browse for your file, select the named range MD (to put the field names on the RHS) and then proceed until you can select the option to go into MS Query and hit finish. Within MS Query hit the SQL button and replace the text by the new SQL below. OK that and then hit the 'open door' icon to return the results to Excel. This is now a refreshable query - so like a pivot table, hit data refresh to update it if required. I'll separately post some VBA to do this using late bound ADO. HTH, Fazza

SELECT Max(MD.PurchaseDate) AS [PurchaseDate], MD.Amt
FROM MD MD, (SELECT Max(A.Amt) AS [Amt]
FROM MD A) B
WHERE MD.Amt = B.Amt
GROUP BY MD.Amt

 

[Fazza]

Some late bound ADO. Please modify to suit, Fazza

Sub via_ADO()

  Dim i As Long
  Dim strConn As String
  Dim strSQL As String
  Dim objRS As Object
  Dim wbkNew As Workbook

  Range("A1").CurrentRegion.Name = "MD"

  strConn = Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
      ActiveWorkbook.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

  strSQL = Join$(Array( _
      "SELECT Max(MD.PurchaseDate) AS [PurchaseDate], MD.Amt", _
      "FROM MD MD, (SELECT Max(A.Amt) AS [Amt]", _
      "FROM MD A) B", _
      "WHERE MD.Amt = B.Amt", _
      "GROUP BY MD.Amt"), vbCr)

  Set wbkNew = Workbooks.Add(template:=xlWBATWorksheet)

  Set objRS = CreateObject("ADODB.Recordset")
  With objRS
    .Open strSQL, strConn
    wbkNew.Worksheets(1).Cells(2, 1).CopyFromRecordset objRS

    For i = 0 To .fields.Count - 1
      wbkNew.Worksheets(1).Cells(1, i + 1).Value = .fields(i).Name
    Next i

    .Close
  End With
  
  wbkNew.Worksheets(1).Columns(1).NumberFormat = "d-mmm-yy"
  
  Set objRS = Nothing
  Set wbkNew = Nothing

End Sub

 

[Fazza]

PS The data does not need to be sorted before hand. This will be fast enough even with tens of thousands of rows of data. F

 

[Aladin Akyurek]

Have sheet with purchase dates in 'A' and amts in 'B'...found max amt value ok, but when duplicated, need the latest/most recent related date...how?
Thanks to other threads, found and adapted this:

=INDEX(A2:A279,MATCH(MAX(B2:B279),B2:B279,0))

For example, if amt in B150 and B270 are both = "MAX", I need the date from A270, but above MATCH gives me A150 instead.
Entries are in ascending date order, and there may also be duplicates for a given date.

Thanks!
(Excel 2000)

One of:

[1] Control+shift+enter, not just enter...

=INDEX($A$2:$A$279,MAX(IF($B$2:$B$279=MAX($B$2:$B$279),ROW($B$2:$B$279)-ROW($B$2)+1)))

[2] Just enter...

=LOOKUP(2,1/($B$2:$B$279=MAX($B$2:$B$279)),$A$2:$A$279)

 

[jhallgren]

[2] Just enter...

=LOOKUP(2,1/($B$2:$B$279=MAX($B$2:$B$279)),$A$2:$A$279)

Ok, so I tried this and it seems to work for what I need...but...
Just what exactly is it doing?

And is it possible to have the last cell in the range "float" to last used? In other words, I'm adding new rows with new purchases on varying frequency...sometimes once every 2-3 wks, sometimes 2-3 times per day...so I don't want to have to keep updating this formula each time I add a row, and as I understand it, using the $B$279 syntax would cause me to have to do so, right?

THANKS for this formula, BTW!

 

[Aladin Akyurek]

Ok, so I tried this and it seems to work for what I need...but...
Just what exactly is it doing?

http://www.mrexcel.com/forum/showthread.php?t=177131

And is it possible to have the last cell in the range "float" to last used? In other words, I'm adding new rows with new purchases on varying frequency...sometimes once every 2-3 wks, sometimes 2-3 times per day...so I don't want to have to keep updating this formula each time I add a row, and as I understand it, using the $B$279 syntax would cause me to have to do so, right?

THANKS for this formula, BTW!

If you are on Excel 2003, convert $B$2:$B$279 into a list (Data|List|Create List) or if on Excel 2007 into a table. This will take care off your need.

Otherwise...

C2:

=MATCH(9.99999999999999E+307,B:B)

whose result is used in the formula of interest:

=LOOKUP(2,1/($B$2:INDEX(B:B,$C$2)=MAX($B$2:INDEX(B:B,$C$2))),$A$2:INDEX(A:A,$C$2))