Need help on filter or something similar

E

ekkeindoha

What I would like to do are as follows. Lets say I got a list of reasons
entered for cargo offload and the pieces offloaded are also entered. Out of
this data I would like to get the top 10 reasons for offloads.
 
M

Max

One approach ..

Assuming the source offload #s are in A2 down, with corresponding "reason"
in B2 down (A1:B1 are col headers)

First, sort both cols A and B by col A, descending.

Then place in C2:
=IF(B2="","",IF(COUNTIF($B$2:B2,B2)>1,"",ROW()))
(Leave C1 blank)

Put in D2:
=IF(ROW(A1)>COUNT(C:C),"",INDEX(B:B,SMALL(C:C,ROW(A1))))

Put in E2: =IF(D2="","",COUNTIF(B:B,D2))

Select C2:E2, copy down to last row of source data. Col D returns the unique
list of reasons sorted in descending order by the offload nos, while col E
yields the corresponding count of the reasons. D2:D11 gives you the top 10
reasons by the offload nos. If you want the top 10 reasons by their counts,
copy cols D and E & paste special as values elsewhere, then do a sort on both
cols by the counts col in descending order & pick off the first 10 reasons in
the reasons col.
 
Top