Block randomization using formulas

Hi,

Scenario is as follows:

1) Randomize 4 people to blocks of 4 where each person's name comes up only once
2) Randomize 4 people to blocks of 8 where each person's name comes up only twice
etc.

If I didn't want to use blocks then I can use this formula:

Formula:

=INDEX({"Mike","Molly","Dave","Tom"},RANDBETWEEN(1,4))

but I want to maintain the block structure and I'm trying to stay away from vba (e.g. collection) if possible.

Thanks.

abousetta

Hi abousetta,

Find the attached which is your random block of 4.

What do you mean by #2 above? Is that an 8 x 8 block? How do you count "only twice? 4 names in 16 holes makes each come up 4 times.??

Hi Marvin,

In block randomization each person is randomized equally within a block. So for example 4 people randomized to a block of 8 would mean each person's name would come up twice. For example:

1. Mike
2. Molly
3. Dave
4. Tom
5. Dave
6. Mike
7. Molly
8. Tom

What I am hoping to achieve (if possible) is a single formula (or series of formulas) that can be dragged down a column that would essentially do the following:

Formula #1: Randomize between 4 names
Formula #2: Randomize between 3 remaining names
Formula #3: Randomize between 2 remaining names
Formula #4: Last remaining name

In a collection, I would be able to randomize then remove each name that got allocated and then randomize again, etc. until all four names are allocated. I am wondering if there is a formula to do something similar.

Thanks for looking at this.

abousetta

OK

Find attached your answer. Block of 8 where names appear only twice. See if this isn't what you want.

Hi Marvin,

It's not pretty, but here is something like what I mean:

A1:

Formula:

=INDEX({"Sam","Bob","Fred","Sally"},RANDBETWEEN(1,4))

A2:

Formula:

=IF(COUNTIF($B$1:$B1,"Sam")=ROUNDUP(ROW()/4,0),INDEX({"Bob","Fred","Sally"},RANDBETWEEN(1,3)),

  IF(COUNTIF($B$1:$B1,"Bob")=ROUNDUP(ROW()/4,0),INDEX({"Sam","Fred","Sally"},RANDBETWEEN(1,3)),

  IF(COUNTIF($B$1:$B1,"Fred")=ROUNDUP(ROW()/4,0),INDEX({"Sam","Bob","Sally"},RANDBETWEEN(1,3)),

  IF(COUNTIF($B$1:$B1,"Sally")=ROUNDUP(ROW()/4,0),INDEX({"Sam","Bob","Fred"},RANDBETWEEN(1,3)),""))))

A3:

Formula:

=IF(AND(COUNTIF($B$1:$B2,"Sam")=ROUNDUP(ROW()/4,0),COUNTIF($B$1:$B2,"Bob")=ROUNDUP(ROW()/4,0)),INDEX({"Fred","Sally"},RANDBETWEEN(1,2)),

  IF(AND(COUNTIF($B$1:$B2,"Sam")=ROUNDUP(ROW()/4,0),COUNTIF($B$1:$B2,"Fred")=ROUNDUP(ROW()/4,0)),INDEX({"Bob","Sally"},RANDBETWEEN(1,2)),

  IF(AND(COUNTIF($B$1:$B2,"Sam")=ROUNDUP(ROW()/4,0),COUNTIF($B$1:$B2,"Sally")=ROUNDUP(ROW()/4,0)),INDEX({"Bob","Fred"},RANDBETWEEN(1,2)),



  IF(AND(COUNTIF($B$1:$B2,"Bob")=ROUNDUP(ROW()/4,0),COUNTIF($B$1:$B2,"Fred")=ROUNDUP(ROW()/4,0)),INDEX({"Sam","Sally"},RANDBETWEEN(1,2)),

  IF(AND(COUNTIF($B$1:$B2,"Bob")=ROUNDUP(ROW()/4,0),COUNTIF($B$1:$B2,"Sally")=ROUNDUP(ROW()/4,0)),INDEX({"Sam","Fred"},RANDBETWEEN(1,2)),



  IF(AND(COUNTIF($B$1:$B2,"Fred")=ROUNDUP(ROW()/4,0),COUNTIF($B$1:$B2,"Sally")=ROUNDUP(ROW()/4,0)),INDEX({"Sam","Bob"},RANDBETWEEN(1,2)),""))))))

A4:

Formula:

=IF(COUNTIF($B$1:$B3,"Sam")<ROUNDUP(ROW()/4,0),"Sam",

IF(COUNTIF($B$1:$B3,"Bob")<ROUNDUP(ROW()/4,0),"Bob",

IF(COUNTIF($B$1:$B3,"Fred")<ROUNDUP(ROW()/4,0),"Fred",

IF(COUNTIF($B$1:$B3,"Sally")<ROUNDUP(ROW()/4,0),"Sally",""))))

If you select all four cells and drag down, the block randomization will be maintained).

I don't know if there is a simpler way to accomplish this task

I just may have to revert back to the original plan and use vba.

abousetta

Why didn't my answer work for you?
Did you see what does?

Yes I saw your example, but the problem is that I need something simpler to give to others. In your example for each block to be repeated there would have to be as many helper columns or else the blocks are exactly the same. The number of blocks is variable (e.g. usually from 10 to 200 blocks) and therefore the instructions would have to reflect creating an appropriate number of helper columns.

In the past I used to give instructions on creating columns, sorting them against Rand (), etc. but am looking for a simpler solution for people to implement.

Hey,

If you have the list of names "in a block" was the original question. I now see a "block" is a list of these in a single column. The easiest way to randomize this list is to use a helper column next to these names where the formula is =Rand(). Then sort both columns by this new helper column. No macros needed. If each name is to appear twice, copy the list and paste it under itself in the same column and do the above.

When I first read this problem I thought a "block" was an array of cells, ie: 2x2 or 4x4 or 8x8 cells. The above method of using Rand() in a column next to the names is the easiest choice but to get a square array of cells a Match() lookup was needed.

If VBA is allowed then using the helper column is what I prefer but others like to not use helpers and go to a COLLECTION construct to keep from having duplicates.

Answering questions where the objective is "simpler" is always a problem. Simpler to some is harder for others.

Sometimes there is simply not a "simpler" method.