Macro Problem with 2 intertwining rules

[JustARandomGuy]

I need a module to randomly fill in a table (F3:I32) with the numbers 41-80 when I press a button. Each number must appear 3 times each and can only appear no more than once in each row. Can someone please help me and guide me through how to do it? Thanks in advance

 

[shg]

That's a pretty random question.

Why?

 

[JustARandomGuy]

Well I'm making a simulation of playing cards. There are 30 piles of 4 cards made up of 3x40 different cards. Each pile cannot having duplicates and there are 3 copies of each card. Pressing the button randomises the results.

 

[shg]

There are 120 different cards??

Please explain further. There are lots of ways to shuffle a deck of cards.

 

[JustARandomGuy]

There are 40 different cards, with 3 copies of each for a total of 120 cards. I have to slot each card into the 30 row, 4 column table. There cannot be 2 or more copies of one card in the same row.

 

[shg]

There cannot be 2 or more copies of one card in the same row.

Pardon the extraneous question, but why not? That's not random, it's restrictive. What are you trying to model?

And having said that, I'm off to bed, so perhaps someone else can sort it out before I look back in tomorrow.

 

[sdruley]

03.24.2013-11.38.03 - sdruley's library

This is a "pseudo" random table. If you like what you see, let me know and I will try to get to you the modules required to generate it.
The table is generated using a master seed followed by two random manipulations of the table to meet your specifications. So this is a combination of pure random numbers and randomly applied methods. A pure random table, as you requested, is not possible as you have defined it. I believe that shg's comments also begin to question the feasibility of a pure random table in this sense. If shg starts to question something then we all need to be careful about what we are doing.

 

[JustARandomGuy]

It looks perfect and I would be happy if you could give me what I need to make that table but I don't understand what you mean by 'pseudo' random. What makes it not completely random? Also, will the table work with VLOOKUP? I'm just asking because I've come across some modules in the past that don't work with VLOOKUP.

 

[shg]

Only natural processes (e.g., radioactivity, Brownian motion) are random. Computers use totally deterministic algorithms (e.g., linear congruential generators) to generate long sequences that have (close to) the statistical properties of random numbers.

See Description of the RAND function in Excel

 

[JustARandomGuy]

Ok, I understand. In any case the "pseudo" random table is fine.