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]

does it works for you?

Book1
	A	B	C
1	A	0.632435	3
2	B	0.483275	7
3	C	0.554963	6
4	D	0.664218	2
5	E	0.11623	10
6	F	0.255311	8
7	G	0.60836	4
8	H	0.948971	1
9	I	0.555599	5
10	J	0.169852	9
Sheet2

Cell Formulas
Range	Formula
B1	=RAND()
C1	=RANK(B1,$B$1:$B$10)

 

[zereusz]

does it works for you?

	A	B	C
A
B
C
D
E
F
G
H
I
J

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

[TD="align: right"]0.632435[/TD]
[TD="align: right"]3[/TD]

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

[TD="align: right"]0.483275[/TD]
[TD="align: right"]7[/TD]

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

[TD="align: right"]0.554963[/TD]
[TD="align: right"]6[/TD]

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

[TD="align: right"]0.664218[/TD]
[TD="align: right"]2[/TD]

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

[TD="align: right"]0.11623[/TD]
[TD="align: right"]10[/TD]

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

[TD="align: right"]0.255311[/TD]
[TD="align: right"]8[/TD]

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

[TD="align: right"]0.60836[/TD]
[TD="align: right"]4[/TD]

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

[TD="align: right"]0.948971[/TD]
[TD="align: right"]1[/TD]

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

[TD="align: right"]0.555599[/TD]
[TD="align: right"]5[/TD]

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

[TD="align: right"]0.169852[/TD]
[TD="align: right"]9[/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]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Great it works for me.
Can you shortly explain how?
Thank you very much.

 

[zereusz]

just now I identified that there is possibility to assign same number to same number.

 

[AlanY]

just now I identified that there is possibility to assign same number to same number.

it shouldn't,

fyi, column B produces a set of random numbers between 0 and 1, and Column C ranked them

 

[zereusz]

in case of number 1 (first row) sometimes after refreshing it assigning 1 to 1. anyway it works for my case.

 

[zereusz]

 

[zereusz]

https://ibb.co/kOKRZq
https://ibb.co/mR4ofV

 

[AlanY]

I've misread your requirement and unfortunately the solution provided is not a good one

 

[zereusz]

I've misread your requirement and unfortunately the solution provided is not a good one

It is working but not 100%