rand unique numbers across rows and columns

[HangedManBendy]

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.

 

[James006]

Hi,

You can have a go with following macro :

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

 

[MarcelBeug]

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:

=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:

=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:

=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:

=TRANSPOSE(Q2:U2)

The final matrix will be in R4:V8.
In R4, copy down and to the right through range R4:V8:

=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

 

[HangedManBendy]

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.

 

[MarcelBeug]

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:

=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.

 

[StephenCrump]

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

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Set #				1	2	3	4	5
2	Random				116	51	6	45	88
3	1	2	3	4	5	1	0	0	0		5	4	1	3	2
4	1	2	3	5	4	2	2	0	0		3	1	4	2	5
5	1	2	4	3	5	0	0	0	0		1	2	5	4	3
6	1	2	4	5	3	4	0	0	0		2	5	3	1	4
7	1	2	5	3	4	0	0	0	0		4	3	2	5	1
8	1	2	5	4	3	6	6	0	0
9	1	3	2	4	5	7	0	0	0
10	1	3	2	5	4	8	8	0	0
11	1	3	4	2	5	9	0	0	0
12	1	3	4	5	2	0	0	0	0
13	1	3	5	2	4	11	0	0	0

<tbody>
</tbody>

5x5
​

 

[kalak]

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.

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

 

[HangedManBendy]

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.

 

[MarcelBeug]

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:

=IF(ROWS(C$3:C3)>$B$2,"",RAND())

Formula in D2 copied to the right to AB2:

=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:

=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))