+- MacResource (https://forums.macresource.com)
+-- Forum: My Category (https://forums.macresource.com/forumdisplay.php?fid=1)
+--- Forum: Tips and Deals (https://forums.macresource.com/forumdisplay.php?fid=3)
+--- Thread: Need some quick help with Excel (/showthread.php?tid=32549)
Need some quick help with Excel - PeterB - 05-02-2007
Suppose I have a list of fifty or so people, with 14 numerical scores per person, like so:
Person A 10 8 6 8 2 1 3 4 8 9 3 4 6 9
Person B 1 3 8 8 1 9 8 7 8 0 1 2 5 6
Person C 9 4 5 8 9 1 2 3 5 9 8 7 8 6
Person A's name is in column 1, and each score is in a separate column (e.g., 14 columns total for all the scores)
How do I sort the data so that, by person, their individual scores are listed in high-to-low order? I just tried the "Sort" command, but cannot seem to figure out the right settings to get it to do it the way I want, e.g., not sort the people, but sort the scores within each person...)
I'm sure this is something fairly obvious, but somehow I'm not seeing it...
TiA
Re: Need some quick help with Excel - davester - 05-02-2007
Sort works by columns, not rows. You can do an edit/copy/paste special/transpose to get the data into columns, not rows, then do a data/sort on each column individually to rank the scores. You can then do another transpose operation if you want to get everthing back to rows.
Re: Need some quick help with Excel - Greg the dogsitter - 05-02-2007
The sort command should have an option to sort left-to-right, instead of top-to-bottom.
Re: Need some quick help with Excel - PeterB - 05-02-2007
Thanks guys. Indeed there is an option to sort from left-to-right; I used that for one person, then selected the next person's scores and did "Repeat Sort" ... then repeated for the remaining people. Kind of slow and cumbersome to do it that way, but it did work.
Re: Need some quick help with Excel - davester - 05-02-2007
Hey, I never noticed that before (probably because I always have my data in columns. Thanks greg.
Re: Need some quick help with Excel - RAMd®d - 05-02-2007
Greg's Excel kung-fu is strong...
Re: Need some quick help with Excel - tenders - 05-02-2007
The problem with your solution is that you lose the ability to measure how people did relative to each other on their n-th attempt, which may or may not be a significant piece of information.
To avoid this you could also create formulas in the 14 cells adjacent to each person's score and use the =LARGE(range, k) function to choose the first largest, then the second largest, then the third...then drag those formulas down across each person's set of scores.