2nd Largest Value With Criteria and avoiding duplicates

[dannycabrera]

Hello,

I need to figure out how to grab the 2nd largest value with criteria and avoiding duplicates.

essentially i have a list of transactions and I need to say what is this customer's last time they ordered and 2nd to last time they ordered.

Thank you so much!

 

[Aladin Akyurek]

Try to post a small sample along with the expected result.

 

[dannycabrera]

I am looking to get the 2nd Largest Value with Criteria Avoiding Duplicates,

essentially I want to find out every customer in my records their min, max and 2nd to max ship date

This was my original take on it, '={LARGE(IF([Column2]=[@Column2],IF([Ship Date]<[@MAX],[Ship Date]),[@MAX]),2)}
[TABLE="class: cms_table, width: 500"]
<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">[TR]
[TD]SHIP DATE[/TD]
[TD]CUSTOMER[/TD]
[TD]MIN[/TD]
[TD]MAX[/TD]
[TD]2ND MAX[/TD]
[/TR]
[TR]
[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]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]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]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]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]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]1/2/2013[/TD]
[TD]DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/4/2013[/TD]
[TD]DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/4/2013[/TD]
[TD]DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/5/2013[/TD]
[TD]DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/5/2013[/TD]
[TD]DEF[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

​

 

[dannycabrera]

Figured it out I think,

'{=LARGE(IF([Column2]=[@Column2],IF([Ship Date]<[@MAX],[Ship Date])),1)}

Thanks anyway, and sorry for posting twice, it was a mistake I'm new here

 

[dannycabrera]

so this ended up being my formula is there anything that works faster than this? it is really slow

{=IF(ISERROR(LARGE(IF([Column2]=[@Column2],IF([Ship Date]<[@MAX],[Ship Date])),1)),[@MAX],LARGE(IF([Column2]=[@Column2],IF([Ship Date]<[@MAX],[Ship Date])),1))}

 

[Aladin Akyurek]

[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]))