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)}

SHIP DATE	CUSTOMER	MIN	MAX	2ND MAX
1/1/2013	ABC	1/1/2013	1/3/2013	1/2/2013
1/2/2013	ABC	1/1/2013	1/3/2013	1/2/2013
1/2/2013	ABC	1/1/2013	1/3/2013	1/2/2013
1/3/2013	ABC	1/1/2013	1/3/2013	1/2/2013
1/3/2013	ABC	1/1/2013	1/3/2013	1/2/2013
1/2/2013	DEF	1/2/2013	1/5/2013	1/4/2013
1/2/2013	DEF
1/4/2013	DEF
1/4/2013	DEF
1/5/2013	DEF
1/5/2013	DEF

<tbody style="border-collapse: collapse; border-spacing: 0px 0px; font-size: 13px; margin-bottom: 0px;">
</tbody>

​

 

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

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