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.

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

<tbody>
</tbody>

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