I am a supporter of St. Joseph's hospice. If you find this site useful or if it helped you, consider a small donation to St. Joseph's, please.
Excel © lacks a function to create a list of unique entries. Since such a function comes in very handy every now and then - just think about drop-down lists or lists for data validation - let us create one:
An optional parameter which fills unused cells of the output range with "" seems to be useful.
If you can only use worksheet functions, have a look here.
With sbUniq you can now easily create a rank function without gaps, for example:
If you are interested in downloading a 29 KB Excel 2010 sample file go to my Download page, please.
If you have a huge file with plenty of data you can minimise the runtime by creating a sorted list of unique entries (do not take my UDF GSort - take Excel's internal sort) and then match all input values: In cell D2 you would enter
=MATCH(A2,$C$2:$C$15,1)
and copy down. To inverse the rank order you just need to sort the unique entries descending - but keep in mind that you need to change the last parameter of MATCH to -1!
Other rank solutions I present here.
[Sulprobil] [Get it done] [Organisation] [IT] [Controlling] [Human Resources] [Family] [Contact] [Download] [Disclaimer]