Find top date for unique fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In column A are the names of the cities. In column B are the dates of all of
the events for that city. Here's what I would like to do:

In column C put the earliest date for the unique city
In column D put the latest date for the unique city

So if in column A, Houston is listed 5 times with date ranges from 1/1/2007,
2/1/2007, 3/1/2007, 4/1/2007 and 5/1/2007. In column C, I want 1/1/2007 and
in column D I want 5/1/2007.

There will be multiple cities, so then if there is another city Los Angeles,
then the earliest and latest date should be shown for that also.

Thanks in advance.
 
=MAX(IF($A$1:$A$100=A1,$B$1:$B$100))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

And

=MIN(IF($A$1:$A$100=A1,$B$1:$B$100))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Back
Top