formula

[ericlch16]

I have

column a column b column c
1 2-jan-2011
1 3-jan-2011
2 4-jan-2011
1 5-jan-2011
3 6-jan-2011
2 6-jan-2011

I want a formula in column c so that based on coumn A, it returns the oldest date by column A. for example

column a column b column c
1 2-jan-2011 2-jan-2011
1 3-jan-2011 2-jan-2011
2 4-jan-2011 4-jan-2011
1 5-jan-2011 2-jan-2011
3 6-jan-2011 6-jan-2011
2 6-jan-2011 4-jan-2011

any generic formula for column C?

 

[Scott Huish]

=MIN(IF($A$1:$A$6=A2,$B$1:$B$6))Confirm with CTRL-SHIFT-ENTER rather than just Enter.
Format as date, copy down.

Excel Workbook
	A	B	C
1	1	2-Jan-11	2-Jan-11
2	1	3-Jan-11	2-Jan-11
3	2	4-Jan-11	4-Jan-11
4	1	5-Jan-11	2-Jan-11
5	3	6-Jan-11	6-Jan-11
6	2	6-Jan-11	4-Jan-11
Sheet2

 

[Scott Huish]

Excel Workbook
	A	B	C
1	1	2-Jan-11	2-Jan-11
2	1	3-Jan-11	2-Jan-11
3	2	4-Jan-11	4-Jan-11
4	1	5-Jan-11	2-Jan-11
5	3	6-Jan-11	6-Jan-11
6	2	6-Jan-11	4-Jan-11
Sheet2