formula to display the max (or min value) for an entire column, basedon value in an individual cell

[Dave K]

I am trying to come up with a formula that will display the max (or
min) service date for individuals, based on their SSN. Most
individuals have more than one record in the table.

Is there a formula that will allow me to display the max service date
in the entire column of dates (for that individual SSN)?

The data looks like this.

(A)Service Date (B)Member Max Service Date
4/1/08 SSN1 Need formula here to show max
service date for SSN, looking at all values in A.
5/3/09 SSN2
6/1/08 SSN3
7/1/09 SSN2 (note duplicate)

Thanks for any suggestions.

 

[Pete_UK]

Try this array* formula:

=MAX(IF(B$2:B$100=B2,A$2:A$100))

adjust the ranges to suit your data, then copy down column C.

* An array formula needs to be committed using the key combination of
Ctrl-Shift-Enter (CSE) rather than the usual <enter>. If you do this
correctly then Excel will wrap curly braces { } around the formula
when viewed in the formula bar - do not type these yourself. If you
need to amend/edit the formula, then you will have to use CSE again.

Hope this helps.

Pete