We run Excel spreadsheets and use the basic sort function frequently, however it does not meet all our needs. Hence this request for a macro.
We would like the ability to be able to select a number of cells (a maximum of 40) in a column and convert the numbers in those cells into an order ranking (low to high), but leaving the order of the cells unchanged. Thus if the contents of selected cells M4, M5, M6, M7 and M8 were 3.5, 7.2, 1.4, 10.8 and 2.9, we would like to be able to use a macro to generate the following result M4 3, M5 4, M6 1, M7 5, M8 2. Once the macro had been applied, we need the column to sort as normal should the data in another column be re-sorted using the basic Excel sort function.
1. The contents of the cells to be converted into a rank order are always positive numbers to one decimal place, from 0.1 to 100.0.
2. Sometimes within a run of selected cells two or more will have the same number entered. When this occurs we would like the result to be as follows: if the contents of selected cells M4, M5, M6, M7 and M8 were 3.5, 7.2, 1.4, 10.8 and 3.5, we would like the macro to generate the following result M4 2=, M5 4, M6 1, M7 5, M8 2=.
3. We would like the macro to work in any column of the spreadsheet, not just one specified column.