Randomly choosing a cell from a range of cells?

[emd]

Scenario:

Worksheet A:

ID CODE
1
1
1
2
3
3
4
4
4
....

Worksheet B:
ID CODE
1 AA
1 DF
1 HJ
1 WE
1 RT
1 TY
2 KL
2 HO
2 WO
2 RO
2 PG
2 XM
2 WK
2 DF
....

I want to automatically fill in the CODE column in Worksheet A with a
random value chosen from CODE in Worksheet B; i.e. the 3 CODE values
for ID 1 in Worksheet A must be one of (AA, DF, HJ, WE, RT, TY) chosen
randomly.

Help!

Thanks

 

[JE McGimpsey]

One way:

=INDEX(B!$B$2:$B$1000, MATCH(A2, B!$A$2:$A$1000, FALSE) + INT(RAND()
* COUNTIF(B!$A$2:$A$1000, A2)))

 

[emd]

One way:

=INDEX(B!$B$2:$B$1000, MATCH(A2, B!$A$2:$A$1000, FALSE) + INT(RAND()
* COUNTIF(B!$A$2:$A$1000, A2)))

That is remarkably similar, but simpler, to the way I ended up doing
it.

Thanks very much.

 

[Bernd P]

Hello,

Can you really tolerate that your random lookup can appear more than
once?

Regards,
Bernd