+ Reply to Thread
Results 1 to 15 of 15

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

  1. #1
    Registered User
    Join Date
    10-07-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    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.
    Attached Files Attached Files
    Last edited by nomadic23; 10-07-2013 at 10:31 AM.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    10-07-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    7

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

    Sorry, my mistake. Already uploaded the correct version.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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. #5
    Registered User
    Join Date
    10-07-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    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. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    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. #7
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    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. #8
    Registered User
    Join Date
    10-07-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    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. #9
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    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. #10
    Registered User
    Join Date
    10-07-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    7

    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. #11
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    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. #12
    Registered User
    Join Date
    10-07-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    7

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

    Something like this.
    Attached Files Attached Files

  13. #13
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

    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. #14
    Registered User
    Join Date
    10-07-2013
    Location
    London, England
    MS-Off Ver
    Excel 2007
    Posts
    7

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

    Thank you! You are awesome!

  15. #15
    Forum Expert MickG's Avatar
    Join Date
    11-23-2007
    Location
    Banbury,Oxfordshire
    Posts
    2,650

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

    You're welcome
    Regrds Mick

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. gather elements into homogenous groups
    By Clint46 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-22-2013, 03:15 PM
  2. Randomly dispatch list of employee in three groups according to skills
    By nikenis in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-17-2012, 03:57 PM
  3. Randomly add List Elements to Chart
    By cnicholas in forum Excel General
    Replies: 1
    Last Post: 07-01-2008, 03:09 PM
  4. Replies: 1
    Last Post: 06-16-2008, 05:42 PM
  5. Distributing elements forming n groups
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-20-2005, 09:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1