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

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

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.

2. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

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

4. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

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

5. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

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

6. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

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

7. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

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

8. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

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

9. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

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.

10. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

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?

11. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

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

12. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

Something like this.

13. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

Try this based on your data "210 Elements"
``Please Login or Register  to view this content.``
Regards Mick

14. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

Thank you! You are awesome!

15. ## Re: Randomly group N elements (where N is a factor of 7) into groups of 7

You're welcome
Regrds Mick

There are currently 1 users browsing this thread. (0 members and 1 guests)