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
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.
|Date:||September 22nd, 2005 03:30 pm (UTC)|| |
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.