Generating unique random numbers in Excel without VBA

While there are many VBA (macro) techniques for creating random numbers in Excel, there are many requests for non-macro solutions to this problem.

Below is a somewhat manual technique which, while rather brash, does provide a quick and easy solution for some circumstances, followed by links to more creative and complex examples.  Unfortunately, this is a problem where macros do hold the tidiest solution (though the hardest to understand for non-macro users) and one example is given at the base of this article.

Looking for Microsoft Excel training? Click here for our courses.

  

Using Randbetween and Remove Duplicates - Excel 2007 onwards

Enter the formula =RANDBETWEEN(1,100) into a cell.

Replace 1 with your lowest required number and 100 with your highest required number.

Copy and paste / Ctrl-enter / autofill the formula over as many cells as you desire.

Copythe cells then paste as values (one way to do this is to click the bottom half of the Paste button, select Paste Special, Values then OK.

Highlight the numbers, click the Data tab, click Remove Duplicates then click OK.

You may need to add extra numbers to accommodate the removal of duplicates from the result.

  

More creative and complex solutions

http://www.iansharpe.com/excel-random.php

http://www.mcgimpsey.com/excel/udfs/samplenoreplace.html

http://forums.whirlpool.net.au/archive/1375410

The above has a good clarification of "random numbers" and "random order".

http://chandoo.org/wp/2011/05/04/dummy-data-random-functions/

  

Using VBA

There are many examples of VBA macro code to perform this function, this example is probably one of the easiest to insert and use, and includes instructions on how to add it into your spreadsheet.  Please note there are no guarentees of this working for your needs or that it will cause no harm in your spreadsheet.  As always, no responsibility is expressed or implied and you use this code at your own risk.

http://www.ozgrid.com/VBA/RandomNumbers.htm

  

Nicholas Jankovic

T7 Training Systems

  

Tuesday, 3rd January 2012

 

HomeInformation and pricingCoursesMake a bookingBlogContact us
© 2011 T7 Training Systems

Magicdust Responsive Website Design

HomeInformation and pricingCoursesMake a bookingBlogContact us