Hi Jeremy,
Not a problem, i'll try to break things down.
PivotTables
First, a PivotTable is a analysis tool within XL. It's meant to take a block of data and allow you to filter/compare different fields pretty easily. One nice things is that it supports drag-and-drop type behavior, so if you have something in the column area, but want it in the rows, it's fairly easy to move. A basic introduction to setting up a PivotTable can be found here:
http://www.contextures.com/CreatePivotTable.html
Our File
In the file I sent you, on the Pivot Worksheet, you can (hopefully) see the PT which has the Band and Album fields, and then the score. To get a true "top 20", if might help to remove the Band field (can drag it out of the way, or use the Field List on right of screen), and if we sort Album then descending by score, we'll have a list like:
which I think is our end goal.
Now, how did we get there?
As I said, merging the data into a single location is pretty much critical. You
could do it manually, but neither you nor I have time to do that, so let's let the lazy computer do it for us via a macro, eh?
As I'm a lazy programmer, I used the code from here:
http://www.rondebruin.nl/win/s3/win002.htm
which was a pre-built macro that I was able to use. Not to get into inner workings, but basically it scans through all the sheets and copies a pre-defined range. The one important part of the code you
might need to change is about half-way down, and looks like this:
Code:
'Fill in the range that you want to copy
Set CopyRng = sh.Range("A5:O24")
This is where I told the macro what range to copy. With your workbook, the score of interest I thought was col O, hence why I only went that far. In the attached, I ammend that to grab all the way to col R so we can get the Rank.
Now that we have the data all merged, we want to calculate the score per the idea I proposed in my first post above, where a rank 1 gets the most "points". Since we only have 20 songs each week, the rank 1 song gets 20 points, rank 20 song gets 1 point. In the DoItAll macro, you'll see this line
Code:
'Song list is a Top 20, so subtract from 21
.Range("S2:S" & LastRow(ws)).FormulaR1C1 = "=21-RC[-1]"
Not to get hung up in the mechanics of the code, but it's basically saying to put a formula in col S that does
=21-R2
It's a little confusing because formula uses what's known as RC notation (row/column), but most of us use A1 notation (columns = letters, rows = numbers). Anyway, that gives us the score we want. The PivotTable is then able to grab that field, and do our analysis.
How to use for future use?
Assuming that your data keeps coming to you in multiple sheets, you can use the macro in the file already, or merge it manually. Either way is fine. If using the macro, the onle thing to pay attention to, as noted above, is the range to copy, and
all the sheets should have identical layout. If one sheet had an extra column inserted, this would mess things up.
Once the data is all merged, again, you can do what you want, but the PivotTable in this file is already setup to look at the RDBMergeSheet. right-click on the PT, and hit refresh, and it will load in the new data, and you should be good to go.
Looking up, that was a lot of text, so take a read through and let me know what questions you have.
We're here to help you learn.
