I'm trying to create several 10X10 grids of numbers from 1-100 in random order.

Is there an easy way to do this in excel or some other Office product? If not, do you have any suggestions on how I could do this?

=RANDBETWEEN(1,100)

Every time the sheet recalculates, new random numbers will be generated.

So how do I limit it so that no numbers are duplicated?

Im trying to have a 10X10 grid with all the numbers from 1-100 and no duplicates.

nai06 said:

---

So how do I limit it so that no numbers are duplicated?

Im trying to have a 10X10 grid with all the numbers from 1-100 and no duplicates.

---

Column A is the numbers 1-100
Column B is the Randbetween numbers.
Sort by column B

Tailgate88 said:

---

nai06 said:

---

So how do I limit it so that no numbers are duplicated?

Im trying to have a 10X10 grid with all the numbers from 1-100 and no duplicates.

---

Column A is the numbers 1-100
Column B is the Randbetween numbers.
Sort by column B

---

If you are going to do this and entropy is important to you, I'd make the randbetween (1-1000000)

Otherwise all collision will always be in sequential order.

Basically of column B has two cells with a "5" when you sort it, the lower value A will always come first.

And then add a 10x10 static referenced grid on another tab and you can generate all the grids you want

Quote:

---

Sub numlist()
Dim normlist() As Integer
Dim randlist(1 To 100) As Integer
Dim i As Integer
Dim j As Integer
Dim lb As Integer
Dim ub As Integer
Dim position As Integer

ReDim normlist(1 To 100) As Integer
lb = 1

For i = 1 To 100
normlist(i) = i
Next i

For i = 1 To 100
ub = UBound(normlist)
If ub = 1 Then
randlist(i) = normlist(1)
Exit For
End If
position = Int((ub - lb + 1) * Rnd() + lb)
randlist(i) = normlist(position)
For j = position To ub - 1
normlist(j) = normlist(j + 1)
Next j
ReDim Preserve normlist(1 To (ub - 1)) As Integer
Next i

For i = 1 To 10
For j = 1 To 10
Cells(i, j) = randlist(i + (j - 1) * 10)
Next j
Next i

End Sub

---

It's ugly, but it works. Creates an ordered list from 1-100. Picks a random point and saves it in a new list, then removes that point from the ordered list. Keeps going until the ordered list is completely used up.

Quick and dirty, no VBA:

Column A (rows 1-100) is just =Rand(). This will generate 100 random numbers.

Column B is =rank(a1,$a$1:$a$100). Copy column B down. (Or for a 10 by 10 grid, column c is same formula except starting at at a11, d starts at a21, and so on with l starting at a91, copy down 10 rows)

F9 to recalculate and generate a new grid.

You are going to get collisions with that.

Try pasting this into VBA.

Quote:

---

Sub RandMatrix()
Dim qArray() As Long
ReDim qArray(1 To 100)

qArray() = RandomNumberArray

End Sub

Function RandomNumberArray()
Dim i As Long, n As Long
Dim numArray(1 To 100) As Long
Dim numCollection As New Collection

With numCollection
For i = 1 To 100
.Add i
Next
For i = 1 To 100
n = Rnd * (.Count - 1) + 1
numArray(i) = numCollection(n)
.Remove n
Next
End With

RandomNumberArray = numArray()

Dim x As Integer
Dim y As Integer
Dim r As Integer
x = 1
y = 1
r = 1

For y = 1 To 10
For x = 1 To 10
Cells(x, y).Value = numArray(r)
r = r + 1
Next x
Next y


End Function

---

Thanks for the help on this.

Im trying to make some concentration grids for some student athletes

You want to implement the "Fisher-Yates" shuffle.

https://en.m.wikipedia.org/wiki/FisherYates_shuffle

Should only take 3-4 lines of code.

The odds of collision (if by that you mean repeat numbers) are extremely low, probably on the order of hitting the Powerball.

aggietony2010 said:

---

The odds of collision (if by that you mean repeat numbers) are extremely low, probably on the order of hitting the Powerball.

---

You are right. I read through the code too fast.

Starting with cell 1, choose a random cell not equal to 1 and swap their contents. Repeat for cell 2, ..., cell 100.

Or you could just use the google. https://www.random.org/sequences/ And then paste that into excel. Repeat as many times as needed.

Really simple using R. Download it for free and run this (change the 5 to however many grids you want, and change the path to your local path):

reps<-5
path<-"C://Users//Quailmeezy//Documents"
for( i in 1:reps){
grid<-matrix(sample(1:100, size=100,replace=F), nrow=10,ncol=10)
write.csv(grid, file = paste(path,"grid",i,".csv"))
}