Custom function to find ranked subtotals

[cla99009]

I want to write a custom function that will return a ranked subtotal list.

For example, each month I run a report of all our volume and revenue by job
ID for the current month, prior year, year-to date, and prior year
year-to-date. Each of the four reports has this basic layout (but about
22,000 lines long):

Division Client Type State Units Revenue
GOV Client A IHS AZ 53 6775.89
FIS Client A SOL AZ 15.25 5634.54
ANS Client B SSG MA 23.75 6178.19
NSO Client C MSG PA 6 9264.9
FIS Client B SSG KY 3 985.73
MDO Client C SOL PA 20 31530.64
SS1 Client A MSG WA 8 4776.41
RDO Client D MSG NY 4.13 2469.73
FIS Client B HOS IN 3 725.65


Then I have to make a pivot table and find the top five clients, states, and
client types for each division for each report period in unit volume and
revenue. For each division, that's twenty top five lists that I have to
find, and paste into a report form. I could write a macro to do the leg work
of the recalculate based on the needed criteria and copy/paste the values
into the report, but I would rather build a custom function that will
subtotal the client list, and return which rank I specify.

=RETURNRANKED(ClientsRange, UnitsRange, Rank)

I've though of how I could find the max using a combination of vlookup and
sumif, but not 2, 3, 4 and 5.

Can such a function be written, or should I just program the macro to copy &
paste for me?

 

[Bernie Deitrick]

Use a formula like this in G2 to rank values in column F based on values in B and D:

=1+SUMPRODUCT(($B$2:$B$2000=A2)*($D$2:$D$20000=D2)*($F$2:$F$20000>F2))

Then apply data filters, and show values less than or equal to 5 in column G, and choose your
ccolumn B and column D values to limit what is shown.

HTH,
Bernie
MS Excel MVP