How to find minimum and maximum values in Column B based on a specific string in column A?

Question

I have a list of items in Column A and have corresponding year and month values in Column B and C respectively. I am looking to find the minimum and maximum year in Column B (based on specific value in Column A), then find the corresponding month in Column C (only for max and min value) and then generate and fill in a Start and End date in Column E and F respectively. E.g. IF @Item = Laptop THEN FIND MIN,MAX of @YEAR and THEN FIND adjacent value in @Month. Finally formulate and display a date accordingly. Could i achieve this using a formula or is it out of scope and is better achieved using a macro?

Answer 1

Here is something that I think should work for any version of Excel, and thanks to the comment from @ScottCarver you can now copy it down and it will put the dates on the first row of each Item:

Enter this in E2 as an array formula and then copy down:

=IF(A2<>A1,MIN(IF(A:A=A2,DATE(B:B,C:C,1))),"")

Enter this in F2 as an array formula and then copy down:

=IF(A2<>A1,MAX(IF(A:A=A2,DATE(B:B,C:C+1,0))),"")

Because these are array formulas you need to press control-shift-enter instead of just enter. After they are entered they will be surrounded by { } to indicate that they are array formulas.

 

Original Answer:

Here is something that I think should work for any version of Excel, but you can't copy it down - you have to enter one separately for every Item. If you really want them spread out so that the dates are on the row of the first occurance of the Item (like in your screenshot) then this won't really work for you.

For the start date of the Laptop Item:

=MIN(IF(A:A="Laptop",DATE(B:B,C:C,1)))

For the end date of the Laptop Item:

=MAX(IF(A:A="Laptop",DATE(B:B,C:C+1,0)))

These are array formulas so you need to press control-shift-enter instead of just enter. After they are entered they will be surrounded by { } to indicate that they are array formulas.

Answer 2

For the Start date, in E2:

=IF(A2<>A1,DATE(AGGREGATE(15,6,$B$1:$B$1000/($A$1:$A$1000=A2),1),AGGREGATE(15,6,$C$1:$C$1000/(($A$1:$A$1000=A2)*($B$1:$B$1000 = AGGREGATE(15,6,$B$1:$B$1000/($A$1:$A$1000=A2),1))),1),1),"")

And for the end date in F2:

=IF(A2<>A1,EOMONTH(DATE(AGGREGATE(14,6,$B$1:$B$1000/($A$1:$A$1000=A2),1),AGGREGATE(14,6,$C$1:$C$1000/(($A$1:$A$1000=A2)*($B$1:$B$1000 = AGGREGATE(14,6,$B$1:$B$1000/($A$1:$A$1000=A2),1))),1),1),0),"")

Then copy down the desired rows.

The aggregate function was introduced in 2010 so it will not work in excel 2007 or earlier.

I over thought they can be simplified to:

=IF(A2<>A1,AGGREGATE(15,6,DATE($B$1:$B$1000,$C$1:$C$1000,1)/($A$1:$A$1000=A2),1),"")

And:

=IF(A2<>A1,AGGREGATE(14,6,EOMONTH(DATE($B$1:$B$1000,$C$1:$C$1000,1),0)/($A$1:$A$1000=A2),1),"")

Thanks to @Jerry for showing a more succinct method.