Array function

[MohitMathur1234]

Hi ,

I am looking for 9x9 Potential so I have found an attached file but in this sheet all array formulas are used, I need any alternative solution means without array formula, Boz it is taking so much time to process and making sheet to heavy. Please help me.

 

[NARAYANK991]

Hi ,

You can dispense with the array formula if your data can be sorted ; is this possible ?

Narayan

 

[Kevin@Radstock]

Maybe a bit faster and non CSE!
=IFERROR(INDEX($A$2:$A$199,AGGREGATE(15,6,ROW($A$2:$A$199)-ROW($A$1)/($B$2:$B$199=E$1),ROWS(E$2:E2))),"")

 

[MohitMathur1234]

Thank you ..!!

 

[p45cal]

And another, but quite different, approach.
Pivot tables.
In the attached in the vicinity of cell L17 there is a block of 3x3 sets of results.
This is actually 9(!) pivot tables.
Change the data in column B and refresh the pivot tables by clicking the button 'refresh' which is near cell I17.
The button's needed because simply refreshing the pivot tables would result in their crashing into each other as their sizes changed. What the button/macro does is briefly move them all into a horizontal line, refreshes them, then moves them back where they were but the 2nd and 3rd rows moved to accommodate the new sizes of the results above them.