+ Reply to Thread
Results 1 to 9 of 9

Random Number with No Repeats

  1. #1
    Registered User
    Join Date
    07-25-2016
    Location
    Washington, PA
    MS-Off Ver
    Microsoft Office 2011
    Posts
    19

    Random Number with No Repeats

    Hi there, I am trying to assign random numbers to a set of data, but I don't want any duplicate numbers. Right now I have it ranking a set of random values, but the goal is to have it just assign a whole integer for each value and not repeat the integer.

    Thanks in advance!

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Random Number with No Repeats

    Can you be more specific...

    Random non-repeating number from ____ to ____?

    And where do you need these random numbers to appear? Be specific!
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    07-25-2016
    Location
    Washington, PA
    MS-Off Ver
    Microsoft Office 2011
    Posts
    19
    Quote Originally Posted by Tony Valko View Post
    Can you be more specific
    The will appear in column A beside any data in column B. The number will be from 1-a cell that has the count function in it. For instance if there are 13 data points I will need the formula to assign random now repetitive numbers from 1-13.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Random Number with No Repeats

    Try this...

    Data Range
    A
    B
    1
    Header
    Header
    2
    5
    Ya
    3
    2
    Ba
    4
    1
    Da
    5
    3
    Ba
    6
    4
    Do


    This array formula** entered in A2:

    =LARGE(ROW(INDIRECT("1:"&COUNTA(B$2:B$100)))*(COUNTIF(A$1:A1,ROW(INDIRECT("1:"&COUNTA(B$2:B$100))))=0),RANDBETWEEN(1,COUNTA(B$2:B$100)+1-ROWS(A$2:A2)))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

    Copy down as needed.

    Adjust for the correct end of range where I've used down to B100.

  5. #5
    Registered User
    Join Date
    07-25-2016
    Location
    Washington, PA
    MS-Off Ver
    Microsoft Office 2011
    Posts
    19
    That worked, is there any way to stop the numbers from continuing to randomly generate everything something new is added?

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Random Number with No Repeats

    The only way I know of is to convert the formulas to constants.

    You would select the formulas>right click>copy>right click>paste special>values.

    However, that might defeat the purpose!

    The only other possibility that I can think of is a VBA function that returns the numbers as static values but I have never seen such a function so I don't know if it's even possible!

    I posted this in the Programming forum:

    https://www.excelforum.com/showthread.php?t=1151314
    Last edited by Tony Valko; 08-11-2016 at 12:00 PM.

  7. #7
    Registered User
    Join Date
    07-25-2016
    Location
    Washington, PA
    MS-Off Ver
    Microsoft Office 2011
    Posts
    19

    Re: Random Number with No Repeats

    Awesome thanks!

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Random Number with No Repeats

    You're welcome!

    Looks like we both got something out of this!

  9. #9
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Random Number with No Repeats

    Here's a macro that will populate column A (beginnning in A2) with random non repeating numbers from 1 to n

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Random pairings with no repeats for a whole semester
    By trent1111 in forum Excel General
    Replies: 3
    Last Post: 05-07-2016, 01:38 AM
  2. VBA random numbers NO repeats
    By jamiegfinch in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 12-18-2015, 12:01 AM
  3. Random number generation across multiple ros/columns without repeats
    By Zodeeak in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-27-2014, 03:15 PM
  4. Record Repeats of random function
    By gutkinma in forum Excel General
    Replies: 4
    Last Post: 09-09-2011, 02:19 AM
  5. Excel 2007 : Random seating chart no repeats
    By jhw69 in forum Excel General
    Replies: 1
    Last Post: 09-04-2011, 02:06 PM
  6. Excel 2007 : Random number generator without repeats
    By HRJames in forum Excel General
    Replies: 4
    Last Post: 03-24-2011, 11:37 AM
  7. selecting and summarising random repeats
    By tghcogo in forum Excel General
    Replies: 10
    Last Post: 02-06-2011, 08:31 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1