[Table="width:, class:grid"][tr][td]Row\Col[/td][td]A
[/td][td]B
[/td][td]C
[/td][td]D
[/td][td]E
[/td][/tr][tr][td]1
[/td][td]SHIP DATE[/td][td]CUSTOMER[/td][td]min[/td][td]max[/td][td]2nd max[/td][/tr]
[tr][td]2
[/td][td]1/1/2013
[/td][td]ABC[/td][td]1/1/2013
[/td][td]1/3/2013
[/td][td]1/2/2013
[/td][/tr]
[tr][td]3
[/td][td]1/2/2013
[/td][td]ABC[/td][td]1/1/2013
[/td][td]1/3/2013
[/td][td]1/2/2013
[/td][/tr]
[tr][td]4
[/td][td]1/2/2013
[/td][td]ABC[/td][td]1/1/2013
[/td][td]1/3/2013
[/td][td]1/2/2013
[/td][/tr]
[tr][td]5
[/td][td]1/3/2013
[/td][td]ABC[/td][td]1/1/2013
[/td][td]1/3/2013
[/td][td]1/2/2013
[/td][/tr]
[tr][td]6
[/td][td]1/3/2013
[/td][td]ABC[/td][td]1/1/2013
[/td][td]1/3/2013
[/td][td]1/2/2013
[/td][/tr]
[tr][td]7
[/td][td]1/2/2013
[/td][td]DEF[/td][td]1/2/2013
[/td][td]1/5/2013
[/td][td]1/4/2013
[/td][/tr]
[tr][td]8
[/td][td]1/2/2013
[/td][td]DEF[/td][td]1/2/2013
[/td][td]1/5/2013
[/td][td]1/4/2013
[/td][/tr]
[tr][td]9
[/td][td]1/4/2013
[/td][td]DEF[/td][td]1/2/2013
[/td][td]1/5/2013
[/td][td]1/4/2013
[/td][/tr]
[tr][td]10
[/td][td]1/4/2013
[/td][td]DEF[/td][td]1/2/2013
[/td][td]1/5/2013
[/td][td]1/4/2013
[/td][/tr]
[tr][td]11
[/td][td]1/5/2013
[/td][td]DEF[/td][td]1/2/2013
[/td][td]1/5/2013
[/td][td]1/4/2013
[/td][/tr]
[tr][td]12
[/td][td]1/5/2013
[/td][td]DEF[/td][td]1/2/2013
[/td][td]1/5/2013
[/td][td]1/4/2013
[/td][/tr]
[/table]
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]))