Trying to make a excel sheet that will seperate a list into small groups

[brang]

Hello,

Im stuck on a problem at the moment and not really sure how to approach it.

Im trying to make a excel template that will take a list of names, changes but generally around 100, and randomly separate them into 4 sets of 8 groups evenly.
This grouping would be repeated 4 times, but there are some conditions.
Firstly, the same person cannot be put into a group again with someone they have previously been grouped with.
Secondly, someone appearing in the first or last groups cannot appear in that group again.

Im still not sure how i should approach this really. Ive been trying to do it via some complex cell formulas but they are quickly becoming overly complex and im not sure if it will actually work which has completely demotivated me. Ive not been looking at VBA but am now thinking its the right route. Ive been thinking of having a master list and then have an attribute of who people have been grouped with before during each of the groupings, and also what number group they were in and then checking against that or something, but im not sure if that is the most efficient solution.

Any help of suggestions would be massively appreciated. I attached an example dataset in case that helps.

Cheers

[Max, Singapore]

Just some thoughts to help start off your complex randomization exercise ..
1. You already have in B13 down: =RAND()
2. To scramble say, the uniques "Number" col E based on col B
you could use this in H13, copied down: =INDEX($E$13:$E$105,RANK(B13,$B$13:$B$105))
Note: Press F9 to re-gen the scramble
3. To quickly assign group numbers (of say 8 items each) to the scrambled data in col H,
Put in I13, copy down: =INT((ROWS($1:1)-1)/8)+1
Note that the last group no could possibly house less than 8 items ...
--------------------------------------
Any worth? Wave it, hit the little star at the bottom left of my responses

[brang]

Hiya Max,

Thanks! thats definitely helped a bit. Ive got the first part pretty much sorted now. I adapted your code a little and made it so it would organise into 32 groups depending on the total amount of people and have it so the number of groups and subgroups can be changed.
I also ended up replacing the rand() cell formula with a VBA option I found since that meant I could have it automatically adjust for varying amounts of rows and also it means that once generated they will be static unless purposely regenerated. With rand() they would change with each update of the worksheet which might be problematic.

The second part - assigning to groups again, but with the conditions - is proving to be a bit of a head scratcher. Just playing with a few different options at the moment, though its not looking promising so far.