How can I find the minium date in column B subject to data in Colu

[Guest]

I want to select the minium date in column B (excluding blanks) and subject
to matching a document type listed in Column A. How can I do this

A B
ABC 28/10/06
ABC 01/01/07
ABC
ABC 25/07/07
ADE 15/05/07
ADE
ADE 28/02/07


Desired Result (separate worksheet)
A B
ABC 28/10/06
ADC 28/02/07

Thanks

 

[mk]

I want to select the minium date in column B (excluding blanks) and subject
to matching a document type listed in Column A. How can I do this

A B
ABC 28/10/06
ABC 01/01/07
ABC
ABC 25/07/07
ADE 15/05/07
ADE
ADE 28/02/07

Desired Result (separate worksheet)
A B
ABC 28/10/06
ADC 28/02/07

Thanks

1. Perform an advanced filter on Column A to select unique values and
copy data to an empty column (ie C). then move these data to the new
worksheet, in column A.
2. Sort data of first worksheet in ascending order based on Column B
3. Go to second sheet and import this formula into cell B1:
=VLOOKUP(A1;Sheet1!A:B;2;0)
4. Copy this formula for every row of the new sheet.

Vlookup returns only the first value it finds for a matching value. If
your data is already sorted in ascending order, it will always return
the minimum date.

Best
(e-mail address removed)

 

[Guest]

Create a Pivot Table. For example:

type date
abc 12/3/2005
abc 6/7/2007
abc
def 10/26/2007
def 10/25/2007
def
ghj
ghj 12/12/2005
ghj 9/9/2007

will produce:

Min of date
type Total
abc 12/3/2005
def 10/25/2007
ghj 12/12/2005

 

[Bob Phillips]

=MIN(IF((Sheet1!$A$1:$A$10=A1*(Sheet1!$B$1:$B$10<>""),Sheet1!$B$1:$B$10))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.


--
HTH

Bob

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