Include a group / further match to an Index, Aggregate

barney_t

New Member
Joined
Feb 24, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi
I am trying in vain to add a further criteria to a formula that will enable the view of the results by group.

I was helped earlier this week on this site to get the formula that would give the product name with the largest week on week gain without the help of the variance column. This works perfectly.
=INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/(($E$2:$E$11-$D$2:$D$11)=LARGE($E$2:$E$11-$D$2:$D$11,ROWS(H$2:H2)))/(ISNA(MATCH(C$2:C11,H$1:H1,0))),1))

I thought i would be able to work out how to add in a further match or group or if criteria to it but have finally given up and I am now seeking further help.

On the attached, I have the original formula that shows that product8 having the highest week on week gain, what I need to know now is with the addition of the type and material columns what the highest gain would be (for example) for a nut and metal product (result would be product1). (yet again without the variance column)

I hope the request is clear and would appreciate any help.

Many thanks

Barney
Book1.xlsx
ABCDEFGH
1typematerialproductunits wk1units wk2var
2nutmetalproduct1253
3boltplasticproduct2253
4washermetalproduct3451top gain
5nutplasticproduct4550product8
6boltmetalproduct585-3
7washerplasticproduct695-4nut in metal top gain?
8nutmetalproduct7105-5nut (ref this cell)
9boltplasticproduct8055metal (ref this cell)
10washermetalproduct9154product?
11nutplasticproduct1065-1
Sheet1 (2)
Cell Formulas
RangeFormula
H5H5=INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/(($E$2:$E$11-$D$2:$D$11)=LARGE($E$2:$E$11-$D$2:$D$11,ROWS(H$2:H2)))/(ISNA(MATCH(C$2:C11,H$1:H1,0))),1))
F2:F11F2=E2-D2
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

mamady

Board Regular
Joined
Sep 23, 2011
Messages
231
Office Version
  1. 365
Platform
  1. Windows
Hi Barney,

Let me know please, if the below satisfies your requirement:

Book1
ABCDEFGHI
1typematerialproductunits wk1units wk2var
2nutmetalproduct1253
3boltplasticproduct2253
4washermetalproduct3451top gain
5nutplasticproduct4550product8
6boltmetalproduct585-3
7washerplasticproduct695-4
8nutmetalproduct7105-5Typenut
9boltplasticproduct8055Materialmetal
10washermetalproduct9154Top Gainproduct1
11nutplasticproduct1065-1
Sheet3
Cell Formulas
RangeFormula
H5H5=INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/(($E$2:$E$11-$D$2:$D$11)=LARGE($E$2:$E$11-$D$2:$D$11,ROWS(H$2:H2)))/(ISNA(MATCH(C$2:C11,H$1:H1,0))),1))
I10I10=INDEX(C2:C11,MATCH(I8&I9&MAXIFS(F2:F11,A2:A11,I8,B2:B11,I9),A2:A11&B2:B11&F2:F11,0))
F2:F11F2=E2-D2


Regards
Yusuf
 

barney_t

New Member
Joined
Feb 24, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi Yusuf
Thank you for taking a look.
Unfortunately I need the result without using column F. (This was included on the sheet to only highlight the result required).
Also, the Large function would be better as I will also need on occasion the 2nd or 3rd best seller.
I Hope you can help further
Many thanks for your time so far
Kind regards
Barney
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
231
Office Version
  1. 365
Platform
  1. Windows
Thank you Barney for the feedback, your request is much clearer now.
I know that you marked your Office Version as 2016, but for the avoidance of doubt, your Excel does not support the Spill feature, correct?

if it does, try the below:

Book1
ABCDEFGHI
1typematerialproductunits wk1units wk2var
2nutmetalproduct1253
3boltplasticproduct2253
4washermetalproduct3451top gain
5nutplasticproduct4550product8
6boltmetalproduct585-3
7washerplasticproduct695-4
8nutmetalproduct7105-5Typenut
9boltplasticproduct8055Materialmetal
10washermetalproduct9154Top Gainproduct1
11nutplasticproduct1065-1
Sheet1
Cell Formulas
RangeFormula
H5H5=INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/(($E$2:$E$11-$D$2:$D$11)=LARGE($E$2:$E$11-$D$2:$D$11,ROWS(H$2:H2)))/(ISNA(MATCH(C$2:C11,H$1:H1,0))),1))
I10I10=INDEX(C2:C11,MATCH(I8&I9&LARGE(E2:E11-D2:D11,1),A2:A11&B2:B11&LARGE(E2:E11-D2:D11,1),0))
F2:F11F2=E2-D2



Best Regards
Yusuf
 

barney_t

New Member
Joined
Feb 24, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Yusuf
Once against thank you for taking the time to help resolve this query. Apologies, where i am ultimately using this formula is on laptop using 365 so i have updated my profile. Thank you for pointing this out- i am new to this.

When I have tried your formula it appears to give result for the first match of the criteria not the Largest - E.G. if i change the data slightly to the below it still gives the result as product1 where as it should now be product2 - any thoughts?

Thank you for your continued support and I hope to hear from you soon
kind regards
barney
Book1.xlsx
ABCDEFGHI
1typematerialproductunits wk1units wk2var
2nutmetalproduct1220
3nutmetalproduct2253
4washermetalproduct3451top gain
5nutplasticproduct4550product8
6boltmetalproduct585-3
7washerplasticproduct695-4nut in metal top gain?
8nutmetalproduct7105-5nutnut
9boltplasticproduct8055metalmetal
10washermetalproduct9154Top Gainproduct1
11nutplasticproduct1065-1
Sheet1 (2)
Cell Formulas
RangeFormula
H5H5=INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/(($E$2:$E$11-$D$2:$D$11)=LARGE($E$2:$E$11-$D$2:$D$11,ROWS(H$2:H2)))/(ISNA(MATCH(C$2:C11,H$1:H1,0))),1))
I10I10=INDEX(C2:C11,MATCH(I8&I9&LARGE(E2:E11-D2:D11,1),A2:A11&B2:B11&LARGE(E2:E11-D2:D11,1),0))
F2:F11F2=E2-D2
Press CTRL+SHIFT+ENTER to enter array formulas.
 

mamady

Board Regular
Joined
Sep 23, 2011
Messages
231
Office Version
  1. 365
Platform
  1. Windows
Hi Barney,

You are most welcome dear, it is my pleasure to support you whenever I can.

It is good that you have Office 365, with Dynamic Formula, such is much easier. Please try the below and let me know:

Book1
ABCDEFGHI
1typematerialproductunits wk1units wk2var
2nutmetalproduct1220
3nutmetalproduct2253
4washermetalproduct3451top gain
5nutplasticproduct4550product8
6boltmetalproduct585-3
7washerplasticproduct695-4
8nutmetalproduct7105-5nut
9boltplasticproduct8055metal
10washermetalproduct9154Top Gainproduct2
11nutplasticproduct1065-12nd Top Gainproduct1
123rd Top Gainproduct7
134th Top Gain 
145th Top Gain 
Sheet1
Cell Formulas
RangeFormula
H5H5=INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/(($E$2:$E$11-$D$2:$D$11)=LARGE($E$2:$E$11-$D$2:$D$11,ROWS(H$2:H2)))/(ISNA(MATCH(C$2:C11,H$1:H1,0))),1))
F2:F11F2=E2-D2
I10:I14I10=IFERROR(XLOOKUP(LARGE(FILTER($E$2:$E$11-$D$2:$D$11,($H$8=$A$2:$A$11)*($H$9=$B$2:$B$11)),ROWS($H$10:H10)),FILTER($E$2:$E$11-$D$2:$D$11,($H$8=$A$2:$A$11)*($H$9=$B$2:$B$11)),FILTER($C$2:$C$11,($H$8=$A$2:$A$11)*($H$9=$B$2:$B$11))),"")


Or the below simplified formula if you don't want to be selectively choosing the ranking:

Book1
ABCDEFGHI
1typematerialproductunits wk1units wk2var
2nutmetalproduct1220
3nutmetalproduct2253
4washermetalproduct3451top gain
5nutplasticproduct4550product8
6boltmetalproduct585-3
7washerplasticproduct695-4
8nutmetalproduct7105-5nut
9boltplasticproduct8055metal
10washermetalproduct9154Top Gainproduct1
11nutplasticproduct1065-12nd Top Gainproduct2
123rd Top Gainproduct7
134th Top Gain
145th Top Gain
Sheet1
Cell Formulas
RangeFormula
H5H5=INDEX($C$2:$C$11,AGGREGATE(15,6,(ROW($C$2:$C$11)-ROW($C$2)+1)/(($E$2:$E$11-$D$2:$D$11)=LARGE($E$2:$E$11-$D$2:$D$11,ROWS(H$2:H2)))/(ISNA(MATCH(C$2:C11,H$1:H1,0))),1))
I10:I12I10=SORTBY(FILTER($C$2:$C$11,($H$8=$A$2:$A$11)*($H$9=$B$2:$B$11)),LARGE(FILTER($E$2:$E$11-$D$2:$D$11,($H$8=$A$2:$A$11)*($H$9=$B$2:$B$11)),ROWS($H$10:H10)),-1)
F2:F11F2=E2-D2
Dynamic array formulas.



Best Regards
Yusuf
 
Solution

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

A couple of other options as you have 365
+Fluff 1.xlsm
ABCDEFGHI
1typematerialproductunits wk1units wk2var
2nutmetalproduct1220
3nutmetalproduct2253
4washermetalproduct3451top gain
5nutplasticproduct4550product8product8
6boltmetalproduct585-3product9
7washerplasticproduct695-4
8nutmetalproduct7105-5nut
9boltplasticproduct8055metal
10washermetalproduct9055product2product2
11nutplasticproduct1065-1product1
12product7
13
Master
Cell Formulas
RangeFormula
H5:H6H5=FILTER(C2:C11,(E2:E11-D2:D11=MAX(E2:E11-D2:D11)))
I5I5=INDEX(FILTER(C2:C11,(E2:E11-D2:D11=MAX(E2:E11-D2:D11))),1)
H10:H12H10=SORTBY(FILTER(C2:C11,(A2:A11=H8)*(B2:B11=H9)),FILTER(E2:E11-D2:D11,(A2:A11=H8)*(B2:B11=H9)),-1)
I10I10=INDEX(SORTBY(FILTER(C2:C11,(A2:A11=H8)*(B2:B11=H9)),FILTER(E2:E11-D2:D11,(A2:A11=H8)*(B2:B11=H9)),-1),1)
F2:F11F2=E2-D2
Dynamic array formulas.
 

barney_t

New Member
Joined
Feb 24, 2021
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi Yusuf and Fluff,

Thank you both, all the formulas work perfectly - I appreciate your efforts in doing this for me. You are both stars.

I now move on to the last stage of the final formula by adding one more calculation which I will attempt by myself but based on my recent lack of successes I may well be posting a new thread again before the day is out!

Once again a big thank you.

kind regards

barney
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,856
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,122
Messages
5,640,242
Members
417,131
Latest member
Seanr19871

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
Top