rand unique numbers across rows and columns

H

HangedManBendy

New Member
Joined
Nov 9, 2007
Messages
5
I've been lurking a while to see if other posts helped me answer this problem. Apologies if this gets too verbose or unclear. It prob will. Phrasing the problem was my first problem!

I've seen ways to randomize unique numbers, but they usually use 1 set of criteria. What I'd like is something that creates random numbers that are unique across rows AND columns.

For my needs a 5 x 5 table is good. In the example below, any given row or column has a unique set / sequence. I hope so anyway, b/c I made it manually. :)

2 4 3 1 5
5 3 2 4 1
1 2 4 5 3
3 5 1 2 4
4 1 5 3 2

(good)

Thanks to other posts, I could rand & rank column A, then rand column B so that B1 <> A1, B2 <> A2, and so on. However B1:B5 may end up with duplicate entries, and I'm not sure how to extend it through C - E.

I would like it to be as random as possible. It will have to be recalculated occasionally to prevent memorization. I saw a cool post that created something like this for randomly pairing ppl on horseshoe teams, with an INDEX and MOD, but the MOD created a pattern that will make the next number in the set too predictable for my needs.

I can try to do it manually every time, but I'm afraid I'll create a "MOD" pattern like so:

2. 4 3 1 5
5 2. 4 3 1
1 5 2. 4 3
3 1 5 2. 4
4 3 1 5 2.

(bad)

Any kind of validation warns me I have a duplicate number, which brings me back to "manually randomizing" the sets.

Hope that made sense, and congrats to anyone that read this far down the post! Any solution or direction is appreciated.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Hi,

You can have a go with following macro :
Code: 
Sub RandomUnique()
Dim i As Integer
Dim j As Integer
Range("A1:E5").ClearContents
Randomize
  For j = 1 To 5
    For i = 1 To 5
      Cells(i, j) = Int((25 * Rnd) + 1)
        Do Until WorksheetFunction.CountIf _
        (Range("A1:E5"), Cells(i, j)) = 1
        Cells(i, j) = Int((25 * Rnd) + 1)
      Loop
    Next i
  Next j
End Sub
 
Upvote 0
I guess James’ solution is not what is being asked for, as it produces 1-25 randomly instead of unique 1-5 in rows AND columns.

Well, I have this solution that may not win the beauty contest, but it’s working!

The idea is to have all 120 possible combinations of numbers 1-5, select 1 of those combinations randomly for row 1, mark all combinations that are still valid for row 2, select 1 combination from those combinations and so.

In A2:E121 type all possible combinations:
1 2 3 4 5
1 2 3 5 4
etcetera (good luck, only 118 rows left to go… :-)).

In F2:J121: random numbers for each row, 0 if combination A-E is invalid due to duplicate elements with previous row(s).
In F2, copy down and to the right through range F2:J121:
Code: 
=K2*RAND()

In K2:O121: indicators (true/false) for each row if the combination in A-E is still valid.
K2:K121 are all trues (these are included for formula consistency in columns F-J).
In L2, copy down and to the right through range L2:O121 the following array formula, confirm with CTRL-SHIFT-ENTER, not just ENTER:
Code: 
=IF(K2,NOT(OR($A2=INDEX($A$2:$A$121,O$2),$B2=INDEX($B$2:$B$121,O$2),$C2=INDEX($C$2:$C$121,O$2),$D2=INDEX($D$2:$D$121,O$2),$E2=INDEX($E$2:$E$121,O$2))))

Indices of selected combinations are in Q2:U2 (so these are 5 numbers between 1-120 corresponding with the selected combinations in A2:E121) and transposed to Q4:Q8.
In Q2, copy to the right through U2, enter the following array formula, confirm with CTRL-SHIFT-ENTER, not just ENTER:
Code: 
=SUM(($F$2:$F$121=MAX($F$2:$F$121))*ROW(1:120))
Select Q4:Q8, enter the following array formula, confirm with CTRL-SHIFT-ENTER, not just ENTER:
Code: 
=TRANSPOSE(Q2:U2)

The final matrix will be in R4:V8.
In R4, copy down and to the right through range R4:V8:
Code: 
=INDEX(A$2:A$121,$Q4)
You can best recognise the validity of the result, by adding a color scale to the result matrix with conditional formatting.

Now just go ahead and start pressing that F9-key :-)
 
Last edited:
Upvote 0
James - Thank you. That is super clean for randomizing a table of non-repeating numbers (which I can use for another problem I think). Like Marcel said, for this problem I would like it to create uniques across rows AND columns in a smaller range. For instance, if it were 4 x 4, it could be playing cards of 4 suits with values Ace(1) - 4. So each created set of 4 random cards would have 1 of each suit, and 1 of each number.


Marcel - Thanks for the reply. I appreciate the step by step instructions. I like seeing it broken down into different functions, so I can track any issues I have with it. Speaking of which... :)


In my sheet L2:O121 are all returning FALSE, which causes G2:J121 to return 0. The array formula in L2 seems to have copied over fine. I enabled iterative calculations. Otherwise, I get an error that "There is a circular reference in an open workbook, but the references that cause it cannot be listed for you."

Currently in K2:K121, I have all TRUE. I see what you are saying, they are all valid permutations to start, so K2:K121 should be TRUE. As a test, I tried changing that to a validation to make sure K2:K121 <> $A$1, so if K is FALSE then F is 0, but it doesn't change anything in L2:O121.

At this point, the indices in Q2:U2 all return the same number.

I've tried changing the row reference in the INDEX statements in L2 like so - from INDEX($A$2:$A$121,O$2) to INDEX($A$2:$A$121,K$2), and so on for the B-E indexes. That creates 44 TRUE values in column L (which is correct), but still none in M - O. THEN if I change R2 from range $F$2:$F$121 to $G$2:$G$121 it creates one more new string of numbers, since G is based on L. It doesn't carry over to S - U though.

Gah. I can taste it. Not sure what I'm doing wrong. Getting this far still reduces the number of remaining iterations greatly.
 
Upvote 0
Oops, while I preparing my post I was still finetuning the solution and adding columns, so column O became column Q. As these characters are look-a-likes, I overlooked that tiny difference...
(Even now I first noticed the difference after comparing the formula strings, in Excel of course...)

So apologies for that.
The correct formula in L2, copy down and to the right through range L2:O121 the following array formula, confirm with CTRL-SHIFT-ENTER, not just ENTER:
Code: 
=IF(K2,NOT(OR($A2=INDEX($A$2:$A$121,Q$2),$B2=INDEX($B$2:$B$121,Q$2),$C2=INDEX($C$2:$C$121,Q$2),$D2=INDEX($D$2:$D$121,Q$2),$E2=INDEX($E$2:$E$121,Q$2))))

So hopefully this was the only mistake and it will work for you after this adjustment. At least I get the same symptoms you mention with the wrong version of the formula.

Remark: no need to turn on iterative calculations. It's switched off with me.
 
Last edited:
Upvote 0
I've used Marcel's approach too, i.e. listing all the permutations and eliminating any where there is a match.

E2: =RANDBETWEEN(1,120)
F2: =LARGE(F3:F122,RANDBETWEEN(1,COUNTIF(F3:F122,">0"))) copy across
F3: {=IF(OR(OFFSET($K$3:$O$3,,,E$1)=$A3:$E3),0,1+ROW()-ROW($3:$3))} Array-entered, copy across and down
K3: =INDEX(Permutations5,OFFSET($E$2,,ROW()-ROW(K$3)),1+COLUMN()-COLUMN($K:$K)) copy across and down
Permutations5: =$A$3:$E$122

Column G, for example, shows that after two random rows have been generated, the available permutations include #2, #6, #8 ...
Here, #6 has been randomly chosen out of the 12 (sometimes 13) possible permutations, so random row 3 = permutation #6 = 1|2|5|4|3.

The workbook attached shows the 5x5 and 6x6 cases: https://app.box.com/s/fjch3a3rvlcpo0jrsflnnvrba624v413

I've generated the permutations using VBA, so in practice I'd probably use VBA to do the whole job.


Excel 2010
ABCDEFGHIJKLMNO
Set #
Random

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]116[/TD]
[TD="align: right"]51[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]88[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]5[/TD]
[TD="bgcolor: #FFFF00, align: center"]4[/TD]
[TD="bgcolor: #FFFF00, align: center"]1[/TD]
[TD="bgcolor: #FFFF00, align: center"]3[/TD]
[TD="bgcolor: #FFFF00, align: center"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]3[/TD]
[TD="bgcolor: #FFFF00, align: center"]1[/TD]
[TD="bgcolor: #FFFF00, align: center"]4[/TD]
[TD="bgcolor: #FFFF00, align: center"]2[/TD]
[TD="bgcolor: #FFFF00, align: center"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]1[/TD]
[TD="bgcolor: #FFFF00, align: center"]2[/TD]
[TD="bgcolor: #FFFF00, align: center"]5[/TD]
[TD="bgcolor: #FFFF00, align: center"]4[/TD]
[TD="bgcolor: #FFFF00, align: center"]3[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]2[/TD]
[TD="bgcolor: #FFFF00, align: center"]5[/TD]
[TD="bgcolor: #FFFF00, align: center"]3[/TD]
[TD="bgcolor: #FFFF00, align: center"]1[/TD]
[TD="bgcolor: #FFFF00, align: center"]4[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00, align: center"]4[/TD]
[TD="bgcolor: #FFFF00, align: center"]3[/TD]
[TD="bgcolor: #FFFF00, align: center"]2[/TD]
[TD="bgcolor: #FFFF00, align: center"]5[/TD]
[TD="bgcolor: #FFFF00, align: center"]1[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
5x5
​
 
Upvote 0
re: Post #1
If you want a square of random integers, non-repeating along rows and down columns, try this code.
Change the number in the second line to whatever postive integer you like, say 1, or 4, or 1000.
Code: 
Sub nonrep_random_square()

Const n& = 7

Dim a&(), u&()
Dim i&, j&, x&, tmp&, y&
ReDim a&(1 To n, 1 To 2), u&(1 To n, 1 To n)

For i = 1 To n
    a(i, 1) = i
    a(i, 2) = i
Next i

For j = 1 To 2: For i = 1 To n
    x = Int(Rnd * (n - i + 1)) + i
    tmp = a(x, j): a(x, j) = a(i, j): a(i, j) = tmp
Next i, j

For j = 1 To n: For i = 1 To n
        y = a(i, 1) + a(j, 1) - 1
        If y > n Then y = y - n
        u(a(i, 2), j) = y
Next i, j

Range("A1").Resize(n, n) = u

End Sub
 
Upvote 0
Stephen & Marcel - Thank you for the updates. It works!

kalak - That's a nice clean VB solution. Much appreciated.


Seriously, this is such a helpful forum. Thanks again everyone.
 
Upvote 0
My last post, just before I leave for a 3-week holiday :)
I translated Kalak’s VBA solution to a formula based solution. All credits for Kalak of course.
Maximum n is 25.
n is in B2.
Formula in C3 copied down to C27:
Code: 
=IF(ROWS(C$3:C3)>$B$2,"",RAND())
Formula in D2 copied to the right to AB2:
Code: 
=IF(COLUMNS($D2:D2)>$B$2,"",RAND())
Formula in D3, array formula - confirm with CTRL-SHIFT-ENTER, not just ENTER - copy down and to the right through range D3:AB27:
Code: 
=IF(OR(ROWS(D$3:D3)>$B$2,COLUMNS($D3:D3)>$B$2),"",1+MOD(SUM(($C$3:$C$27=LARGE($C$3:$C$27,ROWS(D$3:D3)))*ROW(C$1:C$25))+SUM(TRANSPOSE(($D$2:$AB$2=LARGE($D$2:$AB$2,COLUMNS($D3:D3))))*ROW(C$1:C$25)),$B$2))
 
Upvote 0
You must log in or register to reply here.

Similar threads

GomaPile
  • Question Question
Coaches Random Numbers
2
Replies
10
Views
481
Eric W
Eric W
C
  • Solved
Rank Formula
Replies
1
Views
92
myall_blues
myall_blues
J
  • Question Question
how to extract randomly a number based on a matrix
Replies
4
Views
359
Fluff
Fluff
J
  • Locked
  • Question Question
formula for randomly choosing an option
Replies
1
Views
135
Fluff
Fluff
M
  • Question Question
assigning one of three values to cell to get a score of the row
Replies
1
Views
133
dreid1011
dreid1011

Forum statistics

Threads
1,223,238
Messages
6,170,939
Members
452,368
Latest member
jayp2104

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top