Tracking RANK

[Bob]

Every month I create a spreadsheet that ranks customers by past due amounts
owed that I share with our sales team. What's important is the relational
change in an account's position on that list, along with how much they owe.
Obviously if you show up on the list at position 50, spend 9 months on the
list and are now at position 3 the situation is more important than someone
who pops up on the list at position 20, drops to position 50 in the course of
3 months!

I want to create a comparison similar to the way they do the Billboard
record sales charts:
* Account's position this month (easy - use RANK)
* Number of months on the top 20 list (Not sure how to do it)
* On current month's listing, show the position in the previous month's
ranking (probably vlookup?) (I'll do conditional formatting to show worsening
accounts in red vs. improving accounts in green)

I'm looking for advice on constructing this type of workbook: new tabs each
month, and rollover formulas? Or a "master" tab with historical data and a
"current" tab that shows results based on historical tab + current month's
data?

Any guidance someone can provide will be most appreciated.

 

[JLatham]

Bob,
Just some initial thoughts that may give you some ideas. Since I don't know
how long an account may be on your list once it gets there, I sort of tossed
out the idea of having a separate tab for each month; the workbook could get
quite large and difficult to maintain as far as collecting the overall data.

I'm thinking of a 3-sheet solution similar your idea of a master with
historical data and a current tab just as you described:
Presentation Sheet - the one you show the sales staff and looks much like
what you are already using.
Current Month Sheet - where you enter their status for the current month and
have the formulas to RANK them.
Historical Sheet - a little VBA code could be used to move current month's
data onto this sheet which would capture the previous month's RANK, and keep
count of the number of months on the list. This code could even look for new
accounts added to the Current Month Sheet and add them to the other two.
You'd run this code just before starting to update the information on the
Current Month Sheet each month. Of course, you could use simple copy and
paste as long as the lists on the current month and historical data are in
the same sequence.

Pulling the current Month's data would be fairly easy using a VLOOKUP()
formula, while if you had account numbers running down the rows and
month/year entries for the column 'label/header' in row 1 on the historical
sheet, you could use another VLOOKUP() formula to get the data from the
historical sheet.

Getting the information from the Historical might require a bit of ingenuity
to pick up 2 values from it depending on how much historical data you keep.
If you simply keep the previous month's RANK and # of months on the top 20
list, it would be easy. If you keep those values for several months so you
have more historical data to examine if you ever want to, they for each
account, each month would occupy 2 columns. You could use MATCH() for the
Month/Year in row 1 to get the column offset to one of the values (# months

 

[JLatham]

Bob, I have actually put together a couple of workbooks that I think pretty
much do what you are looking for based on your description of needs. If
you'd like to get them to examine, just contact me at (remove spaces)
Help From @ JLatham Site.com