extract only top 15 data (with formula)

[rahulshewale1]

hiii
I attached file.column a has menu head description and column b has sold quantity.can i extract top 15 sold quantity menu head description and sold quantity.

this can be possible with formula....

 

[trprasad78]

Hi Rahulshewale,

have you try DATA > FILTER ?

Kindly check attached file.

 

[rahulshewale1]

I know this is possible with filter. But this can be possible with formula

 

[John Jairo V]

Hi @rahulshewale1 (and @trprasad78 ).

Check file with two options: With helper and without it. Blessings!

 

[bosco_yip]

Another option without helper (all in non-CSE formula),

1] Consider Ties in 15th place.

2] Cell B20 value changed to 75, being the 2nd 15th place, for testing the "Ties" purpose.

3] F1, enter : 15

4] S/n D4, formula copy down :

=IF(F4="","",COUNT(F$4:F4))

5] Menu Item E4, formula copy down :

=IF(F4="","",INDEX(A$2:A$61,MATCH(0,INDEX(COUNTIF(E$3:E3,A$2:A$61)+(B$2:B$61<>F4),0),0)))

6] Total Quantity F4, formula copy down :

=IF(ROWS($1:1)<=COUNTIF(B$2:B$61,">="&LARGE(B$2:B$61,F$1)),LARGE(B$2:B$61,ROWS($1:1)),"")

Regards
Bosco

 

[XLPadawan]

In D2:
=LARGE($B$2:$B$61,ROW(A1)) extend down until you have 15 values
In C2:
=INDEX($A$2:$A$61,MATCH(D2,$B$2:$B$61,0))

Remember to give me a Like if you like my answer!

 

[Naresh Yadav]

Dear Rahul,

pls find the attached sheet and finish the Formula with CTRL+SHIFT+ENTER
Formula to Get the TOP 15 menu Item
=INDEX($A$2:$A$61,MATCH(LARGE($B$2:$B$61,ROW(A1)),$B$2:$B$61,0))

Formula to Get the TOP 15 SOLD Item

=LARGE($B$2:$B$61,ROW(A1))

regards
Naresh

 

[bines53]

Hello friends,

Here's another one,

=INDEX($A$2:$A$61,MATCH(AGGREGATE(14,6,($B$2:$B$61),ROW(D1)),$B$2:$B$61,0),0)

David