Random numbers from specific list

[pranavsir]

Hi Guys,


Appreciate your efforts for building and running this web-forum for people like us. I cam across a situation where need excel guru's help. Below is my question.


I have 2 Groups - Group A and Group B

Group A have 15 unique numbers and Group B have 15 unique numbers as well.

None of the number repeat in any group they are unique in both group(for i.e. Group A - 1 to 15 and Group B 20 to 35)


Now I want to create unique code by merging both group's unique numbers

- Code should have 4 unique numbers from Group B and 1 unique number from Group A (A set of total 5 unique numbers from both group)


Can you guys help me to tell how many unique numbers can be generated and how you could do that (Formula!!)


Regards

Pranav

pranavsir@gmail.com

 

[Faseeh]

Hi pranavsir,


Welcome to the forums. See could this work:

=INDEX($B$1:$B$15,RANDBETWEEN(1,15),0)&"."&INDEX($A$1:$A$15,RANDBETWEEN(1,15),0)&"."&INDEX($A$1:$A$15,RANDBETWEEN(1,15),0)&"."&INDEX($A$1:$A$15,RANDBETWEEN(1,15),0)&"."&INDEX($B$1:$B$15,RANDBETWEEN(1,15),0)

Assuming your data is in between A1:B15.


Regards,

Faseeh

 

[oldchippy]

Hi,


Just a couple of queries guys, sorry to be picky


@ pranavsir, should the second group of the 15 numbers be 20 to 34 or 21 to 35?


@ Faseeh, (good formula) the requirement was for 4 numbers from group B and 1 from group A, so if pranavsir can use your formula idea then


=INDEX($B$1:$B$15,RANDBETWEEN(1,15),0)&"."&INDEX($B$1:$B$15,RANDBETWEEN(1,15),0)&"."&INDEX($B$1:$B$15,RANDBETWEEN(1,15),0)&"."&INDEX($B$1:$B$15,RANDBETWEEN(1,15),0)&"."&INDEX($A$1:$A$15,RANDBETWEEN(1,15),0)

 

[Luke M]

Possible permutations = 15*14*13*12*15 = 491400 permutations.

Not sure the formulas are fool proof, as they may allow the same number from Group A to be chosen.

 

[Luke M]

To ensure that the 4 numbers from Group A are each unique, you could use two helper columns. First helper column is just a random number generator with:

=RAND()

Assuming helper column in in Col C, helper column #2 formula is:

=RANK(C1,C:C)


Number generator formula then is:

=INDEX(A1:A15,MATCH(1,D1:D15,0))&INDEX(A1:A15,MATCH(2,D1:D15,0))&INDEX(A1:A15,MATCH(3,D1:D15,0))&INDEX(A1:A15,MATCH(4,D1:D15,0))&INDEX(B1:B15,RANDBETWEEN(1,15))

 

[SirJB7]

Hi, pranavsir!


Give a look at this file:

https://dl.dropbox.com/u/60558749/Random%20numbers%20from%20specific%20list%20%28for%20pranavsir%20at%20chandoo.org%29.xlsx


It uses helper columns but assures you unique numbers as no item in list B could be repeated.


Hope it helps.


Regards!

 

[Luke M]

Oops, now I see that I swapped the Groups around. Sorry. =(