Return values based on duplicates in multiple columns

[Rkustas]

Hello,

I would like to return the 5 largest "Units Left" values and their corresponding Container Codes and Crop Description's, however am having a hard time capturing the desired output with the duplicates.

Source data is in a pivot table, and I need the duplicates.

Any help or direction would be appreciated.

Please see attached workbook with sample data and desired output.

Thanks, Ryan

 

[azumi]

Hope this helps

 

[rahulshewale1]

Hii @Rkustas ,

Visit below Link.

Excel magic trick 967.


Regard
Rahul shewale

 

[bosco_yip]

Try,

1] In A11, copied right to B11 and all copied down :

=IF($C11="","",INDEX(A$2:A$8,MMULT(SMALL(($C$2:$C$8<>$C11)/1%+ROW($1:$7),COUNTIF($C$11:$C11,$C11)),1)))

2] In C11, copied down :

=IF(ROWS($1:1)<=COUNTIF(C$2:C$8,">0"),LARGE(C$2:C$8,ROWS($1:1)),"")

Regards
Bosco

 

[Rkustas]

Thank you Bosco and Azumi,

Both solutions worked well, I have applied it to many projects.

Thanks!