Find out the earliest date

I have two columns in a table; Plan and Date.

Plan Date
A 01/02/03
B 12/08/05
C 02/04/06
C 06/04/07
C 06/04/07

Now I want the third column, also a date, so that it returns the earliest date for the repeating plan type.

Plan Date New Date
A 01/02/03 01/02/03
B 12/08/05 12/08/05
C 02/04/06 02/04/06
C 06/04/07 02/04/06
C 06/04/07 02/04/06

I would appreciate any help. Thank you.

Try this array formula

=MIN(IF($A$2:$A$20=A2,$B$2:$B$20))

Thank you all for the help. It does the job but I was thinking in terms of VBA code. I wanted to integrate it as a part of my macro that does some other things with a push of a button. Is there a command equivalent to "MIN" in VBA?

Thanks again.

Continuing with the issue. Now I have one more condition to match.

Plan Plan Type Date
A M 9/12/2002
B M 12/11/2003
C M 4/5/2006
C M 3/4/2007
C M 3/4/2007
C S 4/5/2004
C S 3/4/2007

This time I have to return earliest date for repeating Plan that have M plan type.

Plan Plan Type Date New Date
A M 9/12/2002 9/12/2002
B M 12/11/2003 12/11/2003
C M 4/5/2006 4/5/2006
C M 3/4/2007 4/5/2006
C M 3/4/2007 4/5/2006
C S 4/5/2004 4/5/2004
C S 3/4/2007 3/4/2007

I tried following code but didn't get it to work

Please Login or Register  to view this content.

Am I missing something?

I tried that but I still am not getting the desired result. It is also considering "S" plan types.

Plan Plan Type Date
A M 9/12/2002 9/12/02
B M 12/11/2003 12/11/03
C M 4/5/2006 4/5/06
C M 3/4/2007 4/5/06
C M 3/4/2007 4/5/06
C S 4/5/2004 4/5/06
C S 3/4/2007 4/5/06

Yes, I did exactly the same but again with the same result. I just don't get it. If it is not much of a hassle, could you try it and see why its not working? I would really appreciate it.

Thank you.

Thank you for quick response. But if you noticed, dates for two records in the end (Plan = C and Plan Type = S) also changed. I only need to change the dates for those records that meet both criteria; (i.e., Plan = C and Plan Type = M). The last two rows should have original date.

Well, there are several plans that are repeated, so I can't hard code it.

I guess I'll just go through the table manually and change the dates. I would like thank you for your time. You've been a great help.

Shg,

I have attached a sample of my .xls file and let me try to explain what I am trying to do again.

I have 4 fields; Plan Title, Plan Type, Approval Date and New Date.

For every Plan Title that is repeated (Ex. Berkshire Regional Planning Commission) and has Plan Type "M", New Date should be populated with the earliest Approval Date (04/26/05). But if the same Plan Title has "S" or any other Plan Type, it should ignore them (New Date should be populated with original Approval Date)