Double randomization...

[QUINABA]

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?

 

[BuddieB]

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...

 

[QUINABA]

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
*************************************************************

 

[QUINABA]

Can this be done in Excel?

 

[QUINABA]

Let me know if my example is not clear enough.

 

[BuddieB]

I'm still confused.

 

[btadams]

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?

 

[rrdonutz]

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

Hi QUINABA,

If your error message reads:

'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

 

[QUINABA]

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...

 

[tusharm]

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.