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!

 

sbUniq

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:

20110212_PB_01_sbUniq_Screen

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.

20110320_PB_01_sbUniq_Code
20110320_PB_02_sbUniq_Code

With sbUniq you can now easily create a rank function without gaps, for example:

20110212_PB_02_sbUniq_Screen

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.

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

UniqRecords_Code
Sulprobil   Get it done   Contact   Disclaimer   Download