Find second largest number in column

rileybloke

New Member
Joined
Feb 7, 2013
Messages
43
Hi, is there a way to find the second largest number in a column of data?
I need to return the second largest ORD_QTY for only Q quantities, and for each item.
I have tried RANKX but cant seem to get it to work, or is there an easier method?
Thanks all in advance :)


ITEM Date PQX ORD_QTY
121209-0001 01/10/2014 00:00 Q 560
121209-0001 02/10/2014 00:00 Q 560
121209-0001 06/10/2014 00:00 Q 1680
121209-0001 13/10/2014 00:00 Q 2760
121209-0001 20/10/2014 00:00 Q 2800
121209-0001 27/10/2014 00:00 Q 1400
121209-0001 03/11/2014 00:00 Q 2640
121209-0001 10/11/2014 00:00 Q 2640
121209-0001 17/11/2014 00:00 Q 2600
121209-0001 24/11/2014 00:00 Q 2640
121209-0001 01/12/2014 00:00 Q 2640
121209-0001 08/12/2014 00:00 Q 2640
15B533-0002 01/10/2014 00:00 Q 72
15B533-0002 06/10/2014 00:00 Q 144
15B533-0002 13/10/2014 00:00 Q 432
15B533-0002 20/10/2014 00:00 Q 360
15B533-0002 27/10/2014 00:00 Q 144
15B533-0002 03/11/2014 00:00 Q 288
15B533-0002 10/11/2014 00:00 Q 288
15B533-0002 17/11/2014 00:00 Q 288
15B533-0002 24/11/2014 00:00 Q 288
15B533-0002 01/12/2014 00:00 Q 288
15B533-0002 08/12/2014 00:00 Q 360
15B533-0002 15/12/2014 00:00 Q 288
15B533-0002 22/12/2014 00:00 Q 288
15B533-0002 29/12/2014 00:00 Q 288
15B533-0002 05/01/2015 00:00 Q 288
15B533-0002 12/01/2015 00:00 Q 288
15B533-0002 19/01/2015 00:00 Q 288
15B533-0002 26/01/2015 00:00 Q 288
15B533-0002 02/02/2015 00:00 Q 288
15B533-0002 09/02/2015 00:00 Q 288
15B533-0002 16/02/2015 00:00 Q 288
15B533-0002 23/12/2014 00:00 X 1224
15B533-0002 20/01/2015 00:00 X 612
121209-0001 22/09/2014 00:00 P 1280
121209-0001 29/09/2014 00:00 P 2720
121209-0001 06/10/2014 00:00 P 2400
121209-0001 13/10/2014 00:00 P 1920
121209-0001 20/10/2014 00:00 P 2000
121209-0001 27/10/2014 00:00 P 1600
121209-0001 03/11/2014 00:00 P 2720
121209-0001 10/11/2014 00:00 P 2560
121209-0001 17/11/2014 00:00 P 2960
15B533-0002 29/09/2014 00:00 P 765
15B533-0002 27/10/2014 00:00 P 306
15B533-0002 24/11/2014 00:00 P 1071
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Try this:


Excel 2010
ABCDEFGHI
1ITEMDatePQXORD_QTY
2121209-00011/10/20140:00Q560121209-0001Q2760
3121209-00012/10/20140:00Q56015B533-0002Q360
4121209-00016/10/20140:00Q1680
5121209-000113/10/20140:00Q2760
6121209-000120/10/20140:00Q2800
7121209-000127/10/20140:00Q1400
8121209-00013/11/20140:00Q2640
9121209-000110/11/20140:00Q2640
10121209-000117/11/20140:00Q2600
11121209-000124/11/20140:00Q2640
12121209-00011/12/20140:00Q2640
13121209-00018/12/20140:00Q2640
1415B533-00021/10/20140:00Q72
1515B533-00026/10/20140:00Q144
1615B533-000213/10/20140:00Q432
1715B533-000220/10/20140:00Q360
1815B533-000227/10/20140:00Q144
1915B533-00023/11/20140:00Q288
2015B533-000210/11/20140:00Q288
2115B533-000217/11/20140:00Q288
2215B533-000224/11/20140:00Q288
2315B533-00021/12/20140:00Q288
2415B533-00028/12/20140:00Q360
2515B533-000215/12/20140:00Q288
2615B533-000222/12/20140:00Q288
2715B533-000229/12/20140:00Q288
2815B533-00025/1/20150:00Q288
2915B533-000212/1/20150:00Q288
3015B533-000219/01/20150:00Q288
3115B533-000226/01/20150:00Q288
3215B533-00022/2/20150:00Q288
3315B533-00029/2/20150:00Q288
3415B533-000216/02/20150:00Q288
3515B533-000223/12/20140:00X1224
3615B533-000220/01/20150:00X612
37121209-000122/09/20140:00P1280
38121209-000129/09/20140:00P2720
39121209-00016/10/20140:00P2400
40121209-000113/10/20140:00P1920
41121209-000120/10/20140:00P2000
42121209-000127/10/20140:00P1600
43121209-00013/11/20140:00P2720
44121209-000110/11/20140:00P2560
45121209-000117/11/20140:00P2960
4615B533-000229/09/20140:00P765
4715B533-000227/10/20140:00P306
4815B533-000224/11/20140:00P1071
Sheet42
Cell Formulas
RangeFormula
I2=SUMPRODUCT(LARGE(($A$2:$A$48=$G2)*($D$2:$D$48=$H2)*($E$2:$E$48),2))
I3=SUMPRODUCT(LARGE(($A$2:$A$48=$G3)*($D$2:$D$48=$H3)*($E$2:$E$48),2))
 
Last edited:
Upvote 0
Sorry about my post. I have a habit of perusing zero reply posts and not noticing the forum they originated in. I just assume everything is strictly Excel related. Good Luck.
 
Upvote 0
PrincessBride: Is the only way I can be satisfied. If I use my right, over too quickly. :)
 
Upvote 0
Tianbas,Why don't you upgrade?

Our IT has tested PowerPivot V2 and it would work with our Excel and BI but not with Sharepoint. So we decided to live with less DAX formula but can at least use the autorefresh and reporting of Sharepoint.
We have our Sharepoint Enterprise on SQL Server 2008 and our IT will not /can not upgrade this in near future.
 
Upvote 0
Our IT has tested PowerPivot V2 and it would work with our Excel and BI but not with Sharepoint. So we decided to live with less DAX formula but can at least use the autorefresh and reporting of Sharepoint.
We have our Sharepoint Enterprise on SQL Server 2008 and our IT will not /can not upgrade this in near future.

We use SharePoint 2010 to host some of our V2 projects. It did take the resources of several departments at Microsoft support to fix the background daily auto refresh, they have still yet to fix the user command refresh on the site.
 
Upvote 0
What about something like this:
Code:
=
CALCULATE(
    MINX(
      TOPN(
             2,
             Data,
             Data[Ord_Qty]
      ),
      Data[Ord_Qty]
    ),
    Data[PQX] = "Q"
)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top