Random number assignment

[zereusz]

I have 1 excel problem.

• I have 9 different numbers or letters (from "1 to 9" or "a to i").

• For each number I need to assign 1 number from remaining not assigned numbers (same number can not be assigned to itself for example 1 can not be assigned to 1).

• Second requirement is that each number has to be used only 1 time.
Can anyone help me with excel code? Thanks.

 

[AlanY]

this modified version, Cell C11 works out if the numbers were not assigned correctly when its greater than 0.

you can just hit F9 for a new set until C11 = 0

Book1
	A	B	C
1	1	0.006574	10
2	2	0.556973	7
3	3	0.813525	2
4	4	0.434986	8
5	5	0.58297	6
6	6	0.041771	9
7	7	0.767418	3
8	8	0.681314	4
9	9	0.590022	5
10	10	0.85484	1
11			0
Sheet2

Cell Formulas
Range	Formula
B1	=RAND()
C1	=RANK(B1,$B$1:$B$10)
C11	=SUMPRODUCT(--(A1:A10=C1:C10))

 

[zereusz]

this modified version, Cell C11 works out if the numbers were not assigned correctly when its greater than 0.

you can just hit F9 for a new set until C11 = 0

	A	B	C

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.006574[/TD]
[TD="align: right"]10[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0.556973[/TD]
[TD="align: right"]7[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]0.813525[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0.434986[/TD]
[TD="align: right"]8[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]0.58297[/TD]
[TD="align: right"]6[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]0.041771[/TD]
[TD="align: right"]9[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0.767418[/TD]
[TD="align: right"]3[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]0.681314[/TD]
[TD="align: right"]4[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]0.590022[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]0.85484[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]0[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=RAND()[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C1[/TH]
[TD="align: left"]=RANK(B1,$B$1:$B$10)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C11[/TH]
[TD="align: left"]=SUMPRODUCT(--(A1:A10=C1:C10))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I do not understand how but this is working.
Thank you very much.

 

[AlanY]

you're welcome

C11 is to work out how many pairs in A1:A10 and C1:C10 are the same, so when it's 0 the number generated are ok

 

[zereusz]

you're welcome

C11 is to work out how many pairs in A1:A10 and C1:C10 are the same, so when it's 0 the number generated are ok

this is what I do not understand =SUMPRODUCT(--(A1:A10=C1:C10))
is it standard syntax?

 

[AlanY]

the (A1:A10=C1:C10) will produce as array of {false, false, true, false,...etc} and the -- convert them to {0, 0, 1, 0,..etc) so that the sumproduct() can add them up

 

[zereusz]

thank you for explanation.
You are at totally different level .