find most recent date with unique reference excel-CodePudding

I have a list of data (I can't sort, filter or delete it as its used by others) where I want to find the most recent date per account. 20,000 lines of data.

I've used this formula in column C

=IF(MAX(IF($B$2:$B$16=B2,$A$2:$A$16))=A2,"Yes","no")

However it looks at all the data in column B, it doesn't identify the unique accounts. There should be another Yes in cell C3

Does anyone know how I can resolve this?

CodePudding user response：

Use an array formula (ctrl shift enter) like this one:

=IF(MAX(($A$2:$A$16)*($B$2:$B$16=$B2))=A2,"Yes","No")

Explanation:

=IF(
  MAX( # gets max date in the range
    ($A$2:$A$16) * ($B$2:$B$16=$B2)
    # if the value in B matches, it will return the date (date * 1), otherwise 0 (date * 0)
  )=A2, # if max date is the same as the date in current row
  "Yes", # then return "yes"
  "No" # otherwise return "no"
)

CodePudding user response：

Don't you have MAXIFS()?

Formula in C2:

=IF(A2=MAXIFS(A$2:A$7,B$2:B$7,B2),"Yes","No")

Or, with ms365:

=IF(A2:A7=MAXIFS(A2:A7,B2:B7,B2:B7),"Yes","No")

A much better solution may be to look into a PivotTable and use the 'Account' for rows and 'Date' as values where you'd only choose to show the max.