Preference/Favorite/Choice List in Excel?

I%26#039;m trying to create a list of preferred universities in Excel. I%26#039;ve got different columns with all sorts of info like Avg GMAT score, Application Date, etc.





I also want to have a column where I can %26quot;rank%26quot; or assign a %26quot;preference%26quot; as well. i.e. each university has its own unique ranking number assigned by me.





It%26#039;s sort of a %26quot;this is my 1st choice, 2nd choice, etc etc.%26quot; thing.





For example, when I change the ranking for %26quot;NYU%26quot; from %26quot;2%26quot; to %26quot;1%26quot;, it does so while changing the changing the ranking of whatever university was %26quot;1%26quot; to %26quot;2%26quot;.





I can later apply a filter and have them ranked in ascending or descending order. But I can%26#039;t seem to figure out the first part. Any solutions?|||the difficulty with what you are trying to do is...


you are manually entering data into the very cells that you want to automate.


you are looking for vba script that will read the last cell that you entered, look for a duplicate, and make the change.





a different way to do this would be to use conditional formatting to look for duplicates. so when you input you rank, the duplicate rank will automatically highlight., and you can adjust accordingly. the pain would be if UCLA dropped from 2 to 15, then you would have to use the paste special...(subtract 1) button to change the ranks in between.





you dont mention this, but another idea would be to give important columns a score or weghting of importance. then have a total column to add all the scores together, and you can use the =rank() function to automatically adjust the sortable top 10 list.





hope someone has a better solution. good luck.