Find second largest number in column

R

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
 
Sort by date Sort by votes

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You must log in or register to reply here.

Similar threads

G
  • Solved
Rolling min and max column based on column
Replies
5
Views
367
JEC
JEC
C
  • Solved
Calculated field filtered tables
Replies
2
Views
439
comonand
C
W
  • Question
Power Query , get record with last data and 1 more criteria
Replies
1
Views
483
lrobbo314
lrobbo314
G
  • Question
Keep conditional duplicate line
Replies
1
Views
438
johnny51981
J
jimrward
  • Solved
Process times
Replies
6
Views
241
JEC
JEC

Forum statistics

Threads
1,215,337
Messages
6,124,340
Members
449,155
Latest member
ravioli44

Share this page

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