TOP 10 and sort [SOLVED]

[Villalobos]

Hello,

I try to find the top 10 item in the list between two dates by plant and after sort it (the highest frequency should be in the rank1), but I am stuck.
Please, could someone check the file, what is the wrong with it.

Thank you!

 

[Faseeh]

Hi Villalobos,

Try this with CSE:

=LARGE(IF((Date>=$H$2)*(Date<$H$3)*($C$20:$C$73="A"),($D$20:$D$73)),ROW(A1))

..drag down, replace "A" with "B" for plant B, hope that helps.

 

[NARAYANK991]

Hi ,

For the frequencies , you can use these two formulae , entered using CTRL SHIFT ENTER :

Column H : =LARGE(FREQUENCY(IF(Táblázat1[Plant]=$G$6,IF(Táblázat1[Date]>=$H$2,IF(Táblázat1[Date]<=$H$3,Táblázat1[Product]))),IF(Táblázat1[Plant]=$G$6,IF(Táblázat1[Date]>=$H$2,IF(Táblázat1[Date]<=$H$3,Táblázat1[Product])))),ROWS($A$1:A1))

entered in H8 , and copied down.

Column M : =LARGE(FREQUENCY(IF(Táblázat1[Plant]=$L$6,IF(Táblázat1[Date]>=$H$2,IF(Táblázat1[Date]<=$H$3,Táblázat1[Product]))),IF(Táblázat1[Plant]=$L$6,IF(Táblázat1[Date]>=$H$2,IF(Táblázat1[Date]<=$H$3,Táblázat1[Product])))),ROWS($A$1:A1))

entered in M8 , and copied down.

Narayan

 

[Villalobos]

Hello Guys,

@Faseeh
I tested your formulae and unfortunately doesn't work, doesn't give the expected result, please would you be so kind to see the attached file?

@Narayan,
Yours is working fine. I would like to say to you thank you!

 

[Faseeh]

@Villalobos

This is a misunderstanding from my end, i ignored you have mentioned frequency as well.

 

[Villalobos]

Hi,

The problem is solved.
So I just would like to share solution:

for example plant A:

TOP 10 item (column G):
G8 =MODE(IF(Date>=$H$2;IF(Date<=$H$3;Táblázat1[Product]))) + CSE
G9 =MODE(IF(COUNTIF(G$8:G8;Táblázat1[Product])=0;IF(Táblázat1[Plant]=$G$6;IF(Date>=$H$2;IF(Date<=$H$3;Táblázat1[Product]*{1\1}))))) + CSE

and copied down upto G17