Randomly group N elements (where N is a factor of 7) into groups of 7

[nomadic23]

I want to group N elements which is a factor of 7 into groups of 3 randomly. For example, I have 21 elements:

Z S W
P M H
N Y V
J T X
D E F
C R K
A O I

I want to group them into 6 more sets. Now, all in all 7 sets. Group them into other sets without the element repeating in the row it has occupied before. For example, for letter P. "P" has occupied row 2 in set 1, it cannot occupy row 2 anymore in other sets. Therefore, each element must have occupied row 1-7 in all the sets.

Is that possible in excel?

*see attached excel file for the example output

Thank you.

[XOR LX]

Hi,

"Group them into other sets without the element repeating in the row it has occupied before"

In your example, "W" occurs twice in the 1st and 3rd rows, as does "C" in the 5th and 7th rows. Please clarify.

Regards

[nomadic23]

Sorry, my mistake. Already uploaded the correct version.

[XOR LX]

Ok, thanks. But I'm still not too clear - should not each of the elements also occur only once in a given set?

In Set 2, "C" occurs twice.
In Set 3, "W" occurs twice

Regards

[nomadic23]

Sorry again, it's a mistake once again. Each element must only occur once in a given set.

[XOR LX]

Ok,

I've had a look and this is going to be extremely difficult without recourse to VBA, in which I'm afraid I'm not very knowledgeable.

I trust and hope that someone with a bit more expertise in that area will pick up on this post soon.

Sorry I couldn't be of assistance.

Regards

[MickG]

Based on your Initial data in "A1:C7 , try this, it should give you 6 more set of data from "E1" on

Please Login or Register  to view this content.

Regards Mick

[nomadic23]

Wow. Thank you very much. What will I change in the code if I want to increase the number of elements?

[MickG]

It rather depend on what you want to do.
If you just want one alternative set I could perhaps modify the existing code to cover it, but if you wanted to create different sets with various numbers of columns and rows, I would have to rethink how to relate, in a general sense, each data set, to the columns/rows outputs.

[nomadic23]

What if I have 210 elements. Now, 30 groups of 7 per set. With 7 sets once again. An element must again occupy all rows 1-7 in the 7 sets. Is it possible to do that?

[MickG]

Do you mean each set has 7 rows by 30 columns ???
Can you show an example of one set (7x30)

[nomadic23]

Something like this.

[MickG]

Try this based on your data "210 Elements"

Please Login or Register  to view this content.

Regards Mick

[nomadic23]

Thank you! You are awesome!

[MickG]

You're welcome
Regrds Mick