Need List of top 25 over and top 25 under

[mbooth]

I am trying to list the top 25 combinations 'over' and 'under' based on the combination in column O by either using the variance in column R or the amount 'over' and 'under' in columns S and T respectively having the top 25 'over' populate in column U and the top 25 under populate in column V.

Note: the number of top "combinations" needs to be able to change from 25 to 10 etc when needed.

[TABLE="width: 1604"]
<tbody>[TR]
[TD]I[/TD]
[TD]J[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[TD]U[/TD]
[TD]V[/TD]
[/TR]
[TR]
[TD]APO Product No-FRZ[/TD]
[TD]Description[/TD]
[TD]Regional[/TD]
[TD]Regional Name[/TD]
[TD]CONCAT REGIONAL AND MATERIAL[/TD]
[TD]Total Order Qty (cases)[/TD]
[TD]Forecast (cases)[/TD]
[TD]Variance (cases)[/TD]
[TD]3 of cases over fcst[/TD]
[TD]# of cases under fcst[/TD]
[TD]Top 25 Over[/TD]
[TD]Top 25 Under[/TD]
[/TR]
[TR]
[TD]39677230301[/TD]
[TD]PLAIN BAGEL 4.5OZ KOSHER[/TD]
[TD]9200000[/TD]
[TD]ALBERTSONS LLC[/TD]
[TD]920000039677230301[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39677230318[/TD]
[TD]CINNAMON RAISIN BAGEL 4.5OZ KOSHER[/TD]
[TD]9200000[/TD]
[TD]ALBERTSONS LLC[/TD]
[TD]920000039677230318[/TD]
[TD]504[/TD]
[TD]0[/TD]
[TD]504[/TD]
[TD]504[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39677230325[/TD]
[TD]BLUEBERRY BAGEL 4.5OZ KOSHER[/TD]
[TD]9200000[/TD]
[TD]ALBERTSONS LLC[/TD]
[TD]920000039677230325[/TD]
[TD]560[/TD]
[TD]0[/TD]
[TD]560[/TD]
[TD]560[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39677230417[/TD]
[TD]JALAPENO BAGEL 4.5OZ KOSHER[/TD]
[TD]9200000[/TD]
[TD]ALBERTSONS LLC[/TD]
[TD]920000039677230417[/TD]
[TD]840[/TD]
[TD]0[/TD]
[TD]840[/TD]
[TD]840[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39677030307[/TD]
[TD]PLAIN BAGEL 4 5OZ[/TD]
[TD]9200004[/TD]
[TD]OTHER ONTARIO[/TD]
[TD]920000439677030307[/TD]
[TD]7[/TD]
[TD]20[/TD]
[TD]-13[/TD]
[TD]0[/TD]
[TD]-13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]39677030314[/TD]
[TD]CINNAMON RAISIN BAGEL 4 5OZ[/TD]
[TD]9200004[/TD]
[TD]OTHER ONTARIO[/TD]
[TD]920000439677030314[/TD]
[TD]12[/TD]
[TD]20[/TD]
[TD]-8[/TD]
[TD]0[/TD]
[TD]-8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you!!

 

[krykm]

Hi,

I hope that file helps https://www.dropbox.com/s/2hg5phci9hnzw39/ListOfTop25.xlsm?dl=0

 

[mbooth]

Hi Thank you so much!

Question, I changed the formula as I have 3643 rows of data (replacing the 7 rows as advised) I also added column W which is the top 'X' however I noticed that there are two additional columns Max and Min.
What are those being used to reference as when I look at the formula it only references column X (the Max) however nothing from what I can see references column Y (the Min).

Original:
=IF(COUNTIF($X$2:X2,X2)>$W$2,"",INDEX($O$2:$O$7,SMALL(IF(X2=$R$2:$R$7,ROW($R$2:$R$7)-ROW($R$2)+1),COUNTIF($X$2:X2,X2))))

Revised w. rows:
=IF(COUNTIF($X$2:X2,X2)>$W$2,"",INDEX($O$2:$O$3643,SMALL(IF(X2=$R$2:$R$3643,ROW($R$2:$R$3643)-ROW($R$2)+1),COUNTIF($X$2:X2,X2))))

Thank you!!

 

[krykm]

The reference column Y (the min) is used for Top 25 Under (The Lowest 25 Values) . So if you check the V2 the formula, it is;

=IF(COUNTIF($Y$2:Y2,Y2)>$W$2,"",INDEX($O$2:$O$3643,SMALL(IF(Y2=$R$2:$R$3643,ROW($R$2:$R$3643)-ROW($R$2)+1),COUNTIF($Y$2:Y2,Y2))))

Regards

 

[mbooth]

The reference column Y (the min) is used for Top 25 Under (The Lowest 25 Values) . So if you check the V2 the formula, it is;

=IF(COUNTIF($Y$2:Y2,Y2)>$W$2,"",INDEX($O$2:$O$3643,SMALL(IF(Y2=$R$2:$R$3643,ROW($R$2:$R$3643)-ROW($R$2)+1),COUNTIF($Y$2:Y2,Y2))))

Regards

Thanks Again! Sorry I have one more question. Both formulas are working however I am not seeing the 1 - 25 ranking so to speak. It appears as though it is populating with the Concat from Column O.

Really appreciate the help!!

 

[krykm]

It's my mistake sorry. As I told I was in a hurry and didn't have time to check it. So you just need to change the first COUNTIF($Y$2:Y2,Y2)>$W$2 with ROWS($U$2:U2)>$W$2. Finally your formulas are,

For the cell U2 =IF(ROWS($U$2:U2)>$W$2,"",INDEX($O$2:$O$3643,SMALL(IF(X2=$R$2:$R$3643,ROW($R$2:$R$3643)-ROW($R$2)+1),COUNTIF($X$2:X2,X2))))
For the cell V2 =IF(ROWS($V$2:V2)>$W$2,"",INDEX($O$2:$O$3643,SMALL(IF(Y2=$R$2:$R$3643,ROW($R$2:$R$3643)-ROW($R$2)+1),COUNTIF($Y$2:Y2,Y2))))

Regards