Maxifs one less than max, two less than max, etc

Will85

Board Regular
Joined
Apr 26, 2012
Messages
240
Office Version
  1. 365
Platform
  1. Windows
I am trying to create a tool for users to view the $ value of each of the 10 most recent customer orders.

My accounting data is on sheet2: column A contains the customer number, Column B contains the order number, and column C contains the $ value

On sheet1 I have a drop down box in cell A1, this drop down box looks up a unique list of customer numbers contained on sheet3.

On sheet 1 In cells B1:B10 I want each of the 10 most recent $ values of each order. B1 is the most recent, B2 is the second most recent, B3 third most recent, etc.

I can use the maxif formula to find the maximum order number for a given customer, I know that is my most recent order.

However, I dont know what the second most recent order number is for this particular customer.

Im not sure if this is possible to do without a macro. The drop down will be used rapidly to change between customers. I need the results to update as the customer is changed.

Do I need a macro that is triggered by changing my combobox drop down?
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
What version of Excel are you using?
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also can you post some sample data of sheet2
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.
 
Upvote 0
Thanks for that, please don't forget to update your profile ;)
Can you also post some sample data.
 
Upvote 0
The LARGE function would do it. I'm on 365 so can't test for sure, may need to CTRL+ALT+ENTER in Excel 2019. I don't know your skills for naming conventions or table usage, so will refer to the ranges with words you'll have to translate. In B1,
=INDEX(value_array_ref,MATCH(LARGE(((customer_array_ref=customer_selected_ref)+0)*order_array_ref,1),order_array_ref,0))
Assumed order numbers are unique. In B2 the 1 that is k in the LARGE function would be 2, etc.
 
Upvote 0
I know this is not the preferred method, Ill have to work with my IT for future posts to OK the add-on

Here is my raw data:

Customer Number Order Number Sales
1 100 $63,430
2 101 $76,422
3 102 $85,284
1 103 $81,712
1 104 $86,168
1 105 $74,261
2 106 $71,614
1 107 $73,598
3 108 $55,672
3 109 $87,284
3 110 $94,477
3 111 $56,918
1 112 $93,048
1 113 $52,790
2 114 $88,958
2 115 $59,469
1 116 $58,872
3 117 $57,989
3 118 $81,209
4 119 $66,494
2 120 $88,025
2 121 $73,779
3 122 $95,081
1 123 $85,878
1 124 $82,005
1 125 $98,362
3 126 $82,451
3 127 $70,051
3 128 $71,001
1 129 $54,068
1 130 $73,684
2 131 $68,876
2 132 $78,958
1 133 $52,164
3 134 $86,961
3 135 $53,707
4 136 $58,517
2 137 $62,958
2 138 $59,317
3 139 $71,196
1 140 $98,124
1 141 $71,974
1 142 $77,761


Here is the desired result after selecting customer number from a drop down box containing all of my unique customer numbers.
Notice that it is displaying the 10 most recent orders, the most recent being the largest order number for this particular customer.

Order Number Sales
142 $77,761
141 $71,974
140 $98,124
133 $52,164
130 $73,684
129 $54,068
125 $98,362
124 $82,005
123 $85,878
116 $58,872
 
Upvote 0
The LARGE function would do it. I'm on 365 so can't test for sure, may need to CTRL+ALT+ENTER in Excel 2019. I don't know your skills for naming conventions or table usage, so will refer to the ranges with words you'll have to translate. In B1,
=INDEX(value_array_ref,MATCH(LARGE(((customer_array_ref=customer_selected_ref)+0)*order_array_ref,1),order_array_ref,0))
Assumed order numbers are unique. In B2 the 1 that is k in the LARGE function would be 2, etc.
Im actually on 365 as well, forgot we switched to the subscription a while ago. Ill try this right now.
 
Upvote 0
Thanks for that, how about
+Fluff 1.xlsm
ABCDEFGHIJK
1CustomerNumberOrderNumberSales
2110063,430114277761
3210176,42214171974
4310285,28414098124
5110381,71213352164
6110486,16813073684
7110574,26112954068
8210671,61412598362
9110773,59812482005
10310855,67212385878
11310987,28411658872
12311094,477
13311156,918
14111293,048
15111352,790
16211488,958
17211559,469
18111658,872
19311757,989
20311881,209
21411966,494
22212088,025
23212173,779
24312295,081
25112385,878
26112482,005
27112598,362
28312682,451
29312770,051
30312871,001
31112954,068
32113073,684
33213168,876
34213278,958
35113352,164
36313486,961
37313553,707
38413658,517
39213762,958
40213859,317
41313971,196
42114098,124
43114171,974
44114277,761
Test
Cell Formulas
RangeFormula
J2:J11J2=INDEX($B$2:$B$100,AGGREGATE(14,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/($A$2:$A$100=$I$2),ROWS(J$2:J2)))
K2:K11K2=INDEX($C$2:$C$100,AGGREGATE(14,6,(ROW($A$2:$A$100)-ROW($A$2)+1)/($A$2:$A$100=$I$2),ROWS(K$2:K2)))
 
Upvote 0
Ok, with 365 it's even easier
+Fluff 1.xlsm
ABCDEFGHIJK
1CustomerNumberOrderNumberSales
2110063,430114277761
3210176,42214171974
4310285,28414098124
5110381,71213352164
6110486,16813073684
7110574,26112954068
8210671,61412598362
9110773,59812482005
10310855,67212385878
11310987,28411658872
12311094,477
13311156,918
14111293,048
15111352,790
16211488,958
17211559,469
18111658,872
19311757,989
20311881,209
21411966,494
22212088,025
23212173,779
24312295,081
25112385,878
26112482,005
27112598,362
28312682,451
29312770,051
30312871,001
31112954,068
32113073,684
33213168,876
34213278,958
35113352,164
36313486,961
37313553,707
38413658,517
39213762,958
40213859,317
41313971,196
42114098,124
43114171,974
44114277,761
Test
Cell Formulas
RangeFormula
J2:K11J2=INDEX(SORT(FILTER(B2:C100,A2:A100=I2),1,-1),SEQUENCE(10),{1,2})
Dynamic array formulas.
 
Upvote 0
Thank you. I am still playing with it. Are you using the same formula for both columns J and K?

Can you isolate the formula for just column J, just give me the order number? I can use an index match off the order number to populate column K and other needs I may have for other columns that I did not give you. Once I get that descending list of top 10 order numbers, I can index match off of that to pull anything I want.
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,741
Members
448,295
Latest member
Uzair Tahir Khan

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