Row\Col | A | B | C | D | E |
1 | SHIP DATE | CUSTOMER | min | max | 2nd max |
2 | 1/1/2013 | ABC | 1/1/2013 | 1/3/2013 | 1/2/2013 |
3 | 1/2/2013 | ABC | 1/1/2013 | 1/3/2013 | 1/2/2013 |
4 | 1/2/2013 | ABC | 1/1/2013 | 1/3/2013 | 1/2/2013 |
5 | 1/3/2013 | ABC | 1/1/2013 | 1/3/2013 | 1/2/2013 |
6 | 1/3/2013 | ABC | 1/1/2013 | 1/3/2013 | 1/2/2013 |
7 | 1/2/2013 | DEF | 1/2/2013 | 1/5/2013 | 1/4/2013 |
8 | 1/2/2013 | DEF | 1/2/2013 | 1/5/2013 | 1/4/2013 |
9 | 1/4/2013 | DEF | 1/2/2013 | 1/5/2013 | 1/4/2013 |
10 | 1/4/2013 | DEF | 1/2/2013 | 1/5/2013 | 1/4/2013 |
11 | 1/5/2013 | DEF | 1/2/2013 | 1/5/2013 | 1/4/2013 |
12 | 1/5/2013 | DEF | 1/2/2013 | 1/5/2013 | 1/4/2013 |
If we don't use the Table functionality...
In C2
control+shift+enter, not just enter, and copy down:
=MIN(IF($B$2:$B$12=$B2,$A$2:$A$12))
If available to you, with MINIFS
just enter and copy down:
=MINIFS($A$2:$A$12,$B$2:$B$12,$B2)
In D2
control+shift+enter and copy down:
=MAX(IF($B$2:$B$12=$B2,$A$2:$A$12))
If available to you, with MAXIFS
just enter and copy down:
=MAXIFS($A$2:$A$12,$B$2:$B$12,$B2)
In E2
control+shift+enter and copy down:
=MAX($D2,MAX(IF($B$2:$B$12=$B2,IF($A$2:$A$12 < $D2,$A$2:$A$12))))
If available to you, with MAXIFS
just enter and copy down:
=MAX($D2,MAXIFS($A$2:$A$12,$B$2:$B$12, $B2,$A$2:$A$12,"<"&$D2))
With Table functionality...
In C2 control+shift+enter:
=MIN(IF([CUSTOMER]=[@CUSTOMER],[SHIP DATE]))
or using MINIFS, just enter:
=MINIFS([SHIP DATE],[CUSTOMER],[@CUSTOMER])
In D2 control+shift+enter:
=MAX(IF([CUSTOMER]=[@CUSTOMER],[SHIP DATE]))
or using MAXIFS, just enter:
=MAXIFS([SHIP DATE],[CUSTOMER],[@CUSTOMER])
In E2 control+shift+enter:
=MAX([@max],MAX(IF([CUSTOMER]=$B2,IF([SHIP DATE] < [@max],[SHIP DATE]))))
or using MAXIFS, just enter:
=MAX([@max],MAXIFS([SHIP DATE],[CUSTOMER],[@CUSTOMER],[SHIP DATE],"<"&[@max]))