I've always done this manually there must be a better way.
I select the named range, go to sort select the column, copy column b, paste it into the new rank column, copy this column and paste special, values.
as i've got loads of sheets to do this time thought i'd ask if anyone knows of a better way of doing this.
Are you familiar with Excel VBA? It should be fairly easy to write a macro that would automate this task if your data column labels are consistent.
QuoteOriginally posted by thomach
Are you familiar with Excel VBA? It should be fairly easy to write a macro that would automate this task if your data column labels are consistent.
not really, i can understand some vba and can make some modifications but its been a long time since i wrote any code. the other problem is the data isn't consitent in all the spreadsheets, but i suppose it might be easier to edit the worksheet if i could get some sample code.
It may not be the easiest way, but I've a non-VBA method. It will involve you adding an extra column for each column you wish to rank which contains the numbers in order using the SMALL() function. (Substitute for LARGE() if you want to rank in descending order)
In the rank column you then use MATCH() to find the position of the number you are looking for within the ordered list. I have done this for the first column in your spreadsheet. Obviously you should hide the extra columns, (column E in the attached).
HTH, Dzinja
Aha, dkabambe has the right idea. Much easier than VBA. However, I believe you can get there directly with the RANK() function. See column H in the attached versioj of your spreadsheet.
I knew there had to be a function like that, but couldn't for the life of me find it. Have been on Excel all day today and numbers are starting to float off the screen, so methinks it's time to go home! (That's my excuse.)
Have a good evening, dkabambe! And thanks for getting me on track.
However, I just noticed that I made a mistake in my formula (which works now only by chance). The third parameter in the RANK() function controls ascending versus descending sort order. A zero sorts descending, omitting the parameter or entering any non-zero number sorts ascending. (Since Column A has all non-zero numbers my unneeded reference to it happened to work as expected.)
Don’t have an account yet? Register yourself now and be a part of our community!
