Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom function to find ranked subtotals
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom function to find ranked subtotals
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$20000F2)) 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 "cla99009" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Custom function to find top sellers | Excel Programming | |||
RANK Function - Zero Value Ranked as 1 - Should be 10 | Excel Worksheet Functions | |||
find duplicates between rows, keep or delete entries based on ranked relevance | Excel Programming | |||
can't find custom function code | Excel Worksheet Functions | |||
How can I find the last time a custom function is called ? | Excel Programming |