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!
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.
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.Originally Posted by Tony Valko
Try this...
Data Range
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.
That worked, is there any way to stop the numbers from continuing to randomly generate everything something new is added?
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.
Awesome thanks!
You're welcome!
Looks like we both got something out of this!
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
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks