Rank Sml or Lrg > Include Multiple occurences of Value

[Stephan]

Hello, made a great non epos Sales database, that's all formula, just needs a few finishing touches.

How to Rank Priced items in Top 10 of Highest & Lowest?

My spreadsheet Ranks Top Sellers in Price & Quantity, however Index Match only returns Description of nearest, for example when 2 items are same price, only the 1st entry is described, how to edit for multiple occurences?

See Summary tab, after 1st Page, in Cell T33 heading Best Sellers and Top/Btm 25 Sellers.

Currently formula for example for Top 25 Sellers:
=LARGE(STOCKFILE!$T$2:$T$1000,SUMMARY!T35)

SUMMARY!T35 =ROW()-34

=INDEX(STOCKFILE!F$2:F$1000,MATCH(Z35,STOCKFILE!T$2:T$1000,0)

For example the formula states there are 3 top ten sellers at £2.40 within the StockFile tab, but there are only 2. Also the Formula only returns Product Code & Description for nearest top of the list for all of them, saying Cadburys Twirl 3 times instead of:
Cadburys Twirl
Galaxy Ripple

Perhaps an edit of this code, or different Formula?

 

[Jake Collins]

Dear Stephan

Changing the forumla in Z35 to the array formula

=LARGE(STOCKFILE!$T$2:$T$1000-ROW(STOCKFILE!$T$2:$T$1000)/100000,SUMMARY!T35)

and the forumla in AA35 to the array formula

=INDEX(STOCKFILE!F$2:F$1000,MATCH(Z35,STOCKFILE!T$2:T$1000-ROW(STOCKFILE!$T$2:$T$1000)/100000,0))

(and then copying these forumlas down) will resolve the issue of ties.

 

[Stephan]

Hello Jake, thanks, that Formula works with Top Quantity column: rows 35 - 59.

However Edits on the other Ranks don't work with the Array Division, what to edit different?:

Top £ Amount: rows 63 - 87.
Y63 =LARGE(STOCKFILE!$L$2:$L$1000,T63)
AA63 =INDEX(STOCKFILE!F$2:F$1000,MATCH(Y63,STOCKFILE!L$2:L$1000,0))

Btm Quantity: rows 91 - 115.
Z91 =SMALL(IF(STOCKFILE!$T$2:$T$1000>0,STOCKFILE!$T$2:$T$1000),SUMMARY!T91)

AA91 = =INDEX(STOCKFILE!F$2:F$1000,MATCH(Z91,STOCKFILE!T$2:T$1000,0))

Btm £ Amount: rows 119 - 143.
Y119 =SMALL(IF(STOCKFILE!$L$2:$L$1000>0,STOCKFILE!$L$2:$L$1000),SUMMARY!T119)
AA119 =INDEX(STOCKFILE!$A$2:$Q$500,SMALL(IF(STOCKFILE!$A$2:$Q$500=Y119,ROW(STOCKFILE!$L$2:$L$500)-1),ROW(A1)),6)

 

[PaulF]

This is a useful piece and I'm trying to replicate it. I can see the error in the calculation when I'm getting the #N/A, but understand why it works on the previous column ??

 

[Stephan]

Hi Paul sorted that file,
Column H: Rank Number needed Row Formula
Column I: needed absolute & Jake's Formula edit
Column J: needed Jake's Formula edit

On my data set this from Large > Small is accurate, apart from Format Cells in Currency, and then inconsistent in Small > Large, no idea why, hoping Jake would have a Formula!

 

[Jake Collins]

Hello Jake, thanks, that Formula works with Top Quantity column: rows 35 - 59.

However Edits on the other Ranks don't work with the Array Division, what to edit different?:

Top £ Amount: rows 63 - 87.
Y63 =LARGE(STOCKFILE!$L$2:$L$1000,T63)
AA63 =INDEX(STOCKFILE!F$2:F$1000,MATCH(Y63,STOCKFILE!L$2:L$1000,0))

Btm Quantity: rows 91 - 115.
Z91 =SMALL(IF(STOCKFILE!$T$2:$T$1000>0,STOCKFILE!$T$2:$T$1000),SUMMARY!T91)

AA91 = =INDEX(STOCKFILE!F$2:F$1000,MATCH(Z91,STOCKFILE!T$2:T$1000,0))

Btm £ Amount: rows 119 - 143.
Y119 =SMALL(IF(STOCKFILE!$L$2:$L$1000>0,STOCKFILE!$L$2:$L$1000),SUMMARY!T119)
AA119 =INDEX(STOCKFILE!$A$2:$Q$500,SMALL(IF(STOCKFILE!$A$2:$Q$500=Y119,ROW(STOCKFILE!$L$2:$L$500)-1),ROW(A1)),6)

Dear Stephen

The formula for Y63 is

=LARGE(IF(STOCKFILE!$L$2:$L$1000="",0,STOCKFILE!$L$2:$L$1000-ROW(STOCKFILE!$L$2:$L$1000)/100000),SUMMARY!T63)

Formula for AA63 is

=INDEX(STOCKFILE!F$2:F$1000,MATCH(Y63,STOCKFILE!L$2:L$1000-ROW(STOCKFILE!$L$2:$L$1000)/100000,0))

Formula for z91 is

=SMALL(IF(STOCKFILE!$T$2:$T$1000<>0,STOCKFILE!$T$2:$T$1000-ROW(STOCKFILE!$T$2:$T$1000)/100000,""),SUMMARY!T91)

Formula for AA91 is

=INDEX(STOCKFILE!F$2:F$1000,MATCH(Z91,STOCKFILE!T$2:T$1000-ROW(STOCKFILE!$T$2:$T$1000)/100000,0))

formula for Y119 is

=IF(SMALL(IF(STOCKFILE!$L$2:$L$1000<>0,IF(STOCKFILE!$L$2:$L$1000="",MAX(STOCKFILE!$L$2:$L$1000)+1,STOCKFILE!$L$2:$L$1000-ROW(STOCKFILE!$L$2:$L$1000)/100000),""),SUMMARY!T119)>MAX(STOCKFILE!$L$2:$L$1000),"",SMALL(IF(STOCKFILE!$L$2:$L$1000<>0,IF(STOCKFILE!$L$2:$L$1000="",MAX(STOCKFILE!$L$2:$L$1000)+1,STOCKFILE!$L$2:$L$1000-ROW(STOCKFILE!$L$2:$L$1000)/100000),""),SUMMARY!T119))

formula for AA119 is

=INDEX(STOCKFILE!F$2:F$1000,MATCH(Y119,STOCKFILE!L$2:L$1000-ROW(STOCKFILE!$L$2:$L$1000)/100000,0))

 

[Stephan]

Jake, many thanks, that's fab! You're a Genius, looking at those Formula's I wouldn't have guessed, it would be a complicated algorithm.

It's good to see the BTM RANKING! TOP & BTM 10 etc working, at last!

Updated SALES database, SUMMARY pg/tab, includes updated TOP/BTM RANKS. Also added NAVIGATION via BUTTON MACROS, on 1st TAB, NEW SALE, and also aUSERFORM with a motif for new SALES. This simply adds to SALES tab, instead of selecting product bar code for SALES tab via ComboBox.

Thinks it's great, only thing I can think of is for a more advanced UserForm, hence to add/include Multiple items in your basket, then buy. But I've not seen a UserForm that does this.

Also on another version I combined a UserForm select with my generic Data Validation, but on Excel 2003 & my LapTop, it brought it to a grinding halt, but on newer Excel versions & more modern LapTops, it might be great. I'll try the other version out on a newer Laptop, see what I think, if works ok, will upload that.

 

[Stephan]

also talking of reporting from data, I've only solved the biggest riddle ever! Index Match 3D!

I made this simply, normal Index Match, with Nested If for Multiple tabs, then you can just SUM totals, without having to use SUMIF SUMPRODUCT 3D!:

www.hkrebs63.karoo.net/files/IndexMatch3D.zip 2.9MB's

If you need a more detailed explaination, just reply here, I'll let you know, but in plain english see my post about it here:

http://chandoo.org/forum/threads/index-match-3d-chekc-it-out-see-hyperlink.28394/#post-169929