# [SOLVED] Excel Auto Sort Macro



## Rwinnett (Jun 2, 2011)

Hello,

I am trying to sort a results table every time someone updates on of their tabs, which changes the points value in the results. I want it so the person in the lead is always known.

The file was created in excel 2010. 

Also, if you know of a better way to total up the results, that would be awesome to know!

Cheers,
Robert


----------



## majicincells (Jun 10, 2011)

*Re: Excel Auto Sort Macro*

Hi Robert,

Hope you are doing well.

I have reworked your excel sheet and have the data auto sorted in the 'Results' worksheet. I however have not looked at any of your other sheets and am not aware of the other calculations that you have done in this workbook.

The data in the 'Results' sheet is *Not *auto sorted using any macro it is done with a very simple formula, please see the steps below: -

1. In the *'Winning Table'* there were three columns - *'User', 'User's Name' *and *'Total Points'*.
2. I added three more columns in front of the existing columns mentioned in point 1. The columns I added are - *'User Rank', 'Duplicate Check'* and *'Adjusted Rank'*.
3. Under the column 'User Rank' I used the *"=Rank(number,ref)"* formula where I used the Total Points of a user to generate a rank.
4. The rank formula will generate the same rank for two people having the same points. You will see in your table that user *'DJ'* and *'Jasmine'* have the same *'Total Points'* of *3*, therefore both have the rank *3* and user *'Alex'* who has *2* points is ranked *5* so there is no rank *4* because two users have the rank 3.
5. In the *'Dup_Chk'* column I have only checked whether a particular rank is appearing for the first time or more in the User Rank array. This helped me to generate either a *"False"* for every number in the User Rank array appearing for the first time and is giving me a *"True"* for every number in the User Rank array which is appearing for more than 1 time.
6. Based on the *"True"* and *"False"* I adjusted the User Rank in the *'Adjstd_Rank'* column.
7. I created two simple named ranges *'RMin'* with the formula *"=MIN(Results!$A$4:$A$14)"* and *'RMax'* with the formula *"=MAX(Results!$A$4:$A$14)"* where *"Results!$A$4:$A$14"* refers to the range under the User Rank column.
8. Finally, I generated a Serial Number which is exactly the same as the *'Adjstd_Rank'* only sorted from the Min number 1 to the Max in that table and then did a *'VLookup'* where I looked up the Sl. Number in the first table and fetched the remaining data from other columns.

I have also included another example of mine which has a normal table and an auto sorted table, in case you want to make sure how effective the procedure is. Let me know if this helps.

:wave:

Regards,
Niladri Dey


----------



## Rwinnett (Jun 2, 2011)

*Re: Excel Auto Sort Macro*

Thank you very much!!! That worked perfectly for what I need.

Cheers,
Robert


----------

