|
|
|
September 21st, 2005
 | 09:01 pm - Geekery ...
I spent a little time today working out if the British fencing rankings system would work in Australia. First attempt involved a spreadsheet, but it was getting complicated and messy and reached a limit in my (eww) Excel knowledge. Second try was a simple awk script which does the job admirably. If I had to, I could probably whip it into usable shape and wrap a web page around it to make it easy to use. It's fun to tinker ...
So, I have data that looks something like this, listing the placings in each competition :
(about 500 rows of it in total)
| 1st Comp | FencerA | 1 |
| 1st Comp | FencerB | 2 |
| 1st Comp | FencerC | 3 |
| 2nd Comp | FencerD | 1 |
| 2nd Comp | FencerC | 2 |
| 2nd Comp | FencerA | 3 |
And I want to mangle it to look like this :
| | 1st Comp | 2nd Comp |
| FencerA | 1 | 3 |
| FencerB | 2 | |
| FencerC | 3 | 2 |
| FencerD | | 1 |
And it should cope no matter how many competitions or different fencers are in the source list.
Or should I just stick to my awk script ?
Current Mood:  geeky
|
![[User Picture]](https://l-userpic.livejournal.com/122533062/550780) | | From: |  flick |
|---|
| Date: | September 21st, 2005 08:16 pm (UTC) |
|---|
| | | (Link) |
|
Umm... Pivot table? Seriously. No bother. Ah. Hmmm. Don't suppose you have a handy link to somewhere that explains them ? I've seen them around (mostly when flailing around trying to get Excel to do a matrix transpose operation), but still don't really get what they're all about ...
(does it complicate things if, instead of having place numbers in the final table, I want points which are calculated with hlookups, vlookups, and black magic ?)
| | From: | flick |
|---|
| Date: | September 21st, 2005 08:37 pm (UTC) |
|---|
| | | (Link) |
|
I don't have a handy link, I'm afraid (try Google, though: pivot tables are one of those things that have a reputation for being oh-so-scary and hard, so there's probably a lot of help for them).
Doesn't matter what source or format the data's in. Basic version is that you need to have a header for every column. Then highlight the whole lot and go tools-pivot table.
Depending on your version of Excel, you'll get a wizard of some sort. At some point, you need to drag the fields that you want into the pivot table. Fields = column headings, so drag FencerName to the left, CompNumber to the top and Position (I assume - the 123 column) to the middle. It will probably default to Count of Position. Right click on Position and chose (um... I think...) Field Settings (if not that, the one above/below it) and change the option from Count Of to Sum Of (trust me....).
That Should Work.
If it doesn't, it's 'cause I'm drunk; email me the file and I'll do it for you tomorrow.
Ok, the pivot thingy proved to be embarassingly easy to set up - I dragged the name column to rows, the comp. name column to colums, and stuck points in the middle and it magically did the rest - thanks !
For some reason, though, while it automatically gives me a "total points" column on the right, it won't let me sort by that column ... strange.
| | From: | flick |
|---|
| Date: | September 22nd, 2005 03:30 pm (UTC) |
|---|
| | | (Link) |
|
Glad it worked! (Particualrly as I was drunk and didn't have Excel in front of me at the time!)
It's odd that it won't let you sort by the total column but not, I'm afraid, totally unsurprising. Pivot tables can be funny, sometimes.
...
However, I've just had a play, and you can.
Highlight the middle bit of the table (ie the data, the row labels and the row totals) then go to sort. Tell it to sort by one of the cells int he total column, select ascending or descending and it should do it. | | From: | flick |
|---|
| Date: | September 21st, 2005 08:38 pm (UTC) |
|---|
| | | (Link) |
|
But, really, the best way to leave about Pivot Tables is to play with them.
Honest, guv. |
|
