# Double randomization...

#### QUINABA

##### Board Regular
I have 25 sets with 6 letter selection per set. How do I randomly select a letter from a each set then randomly spread it in range A1:E5 (25 cells for the 25 sets) without repeating a set? Does that make sense?

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
can you post some of your data, use COLO's HTML maker to help with the visualization of what you are looking for, you can hard code the results you expect and want formulas for...

Html maker doesn't seem to work on my machine. (Win 98, Excel 97).

Initially there would be no data on a sheet, data would only appear once the code is run. Below is my short example (Range to apply data is A1:B2, 2 rows by 2 column instead of A1:E5, 5 rows by 5 column)

*************************************************************
Set1 ("A","B","C","D","E","F")
Set2 ("A","H","I","J","K","L")
Set3 ("A","N","O","P","QU","R")
Set4 ("A","T","U","V","W","X")

I'd like to randomly pick from each set then apply it randomly to the specified range (A1:B2)

So:
A1 = Can contain a string from one of the four sets

A2 = Can contain a string from one of the four but not in the set used in A1

B1 = Can contain a string from one of the four but not in the set used in A1 and A2

B2 = Can contain a string from one of the four but not in the set used in A1 and A2 and B1
*************************************************************

Can this be done in Excel?

Let me know if my example is not clear enough.

I'm still confused.

Quinaba,

It's possible to do but need more info. Where do these sets of text strings reside? Are they in a spreadsheet or hard-coded in vba?

Originally posted by QUINABA:
Html maker doesn't seem to work on my machine. (Win 98, Excel 97).
Hi QUINABA,

'Compile error:
Can't find project or library'

and the VBA editor opens, then try the following--

When the message appears, go to the Visual Basic editor and select Run / Reset. Then under Tools / References, uncheck the box next to the line that reads:

MISSING: Microsoft Office 9.0 Object Library

You should then be able to run HtmlMaker 2.42. It works for me as I incur a similar problem each time I load a new session of Excel 97 on Windows NT4. I believe this should also work for Excel 97 on Win98. (& Thanks to Brian from Maui for his help on this problem way back when.)

--Tom

Thanks for the reply guys!

Data should preferably reside (hardcoded) in VBA.

I guess the simplest explanation is.... suppose I have 25 six sided dices (I used 4 for my example) but instead of them having numbers they have letters and different in every dice. Now I'd like to throw the dice into a container that has exactly 25 slots. Can you picture it now???

Thanks again...

Suppose your 6 character sets are in F1:F25, with each cell containing a 6 character string. Then, in G1:G25, enter =RAND(). In H1, enter =RANK(G1,\$G\$1:\$G\$25). In I1, enter =MID(INDEX(\$F\$1:\$F\$25,H1),INT(RAND()*6)+1,1). Copy H1:I1 to rows 2:25.

In A1, enter =INDEX(\$I\$1:\$I\$25,(ROW()-1)*5+COLUMN()). Copy A1 to B1:E1. Copy A1:E1 to rows 2:5.

Edit: Adjusted for 6 character strings.

Replies
0
Views
657
Replies
6
Views
467
Replies
8
Views
369
Replies
2
Views
403
Replies
1
Views
136

1,203,673
Messages
6,056,678
Members
444,881
Latest member
Stu2407

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back