# Random Number with No Repeats

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

2. ## 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!

3. Originally Posted by Tony Valko
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. ## 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. That worked, is there any way to stop the numbers from continuing to randomly generate everything something new is added?

6. ## 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:

7. ## Re: Random Number with No Repeats

Awesome thanks!

8. ## Re: Random Number with No Repeats

You're welcome!

Looks like we both got something out of this!

9. ## 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.``

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

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