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.

Information on
St. Joseph's

JustGiving - Sponsor me now!



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
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.

In case you need a Sub to copy all unique records from a column to another one:

Sulprobil   Get it done   Contact   Disclaimer   Impressum   Download