Create unique combinations of individuals for triads

Welcome to the Forum H2D_IRR !

Saying you are assigning people to triads is throwing me off here. Do you mean dividing the participants into teams of 3? When you say 9 groupings, you mean something like dividing them up into 4 teams of 3, and then dividing them again so none of the teams of 3 are the same as the first time, then doing this 9 times?

I suggest attaching a sample file to show us what your input data looks like and a mock-up of what result you want.

Also please update your profile to show an Excel version name, not a release number (like Excel 2019, Microsoft 365).

This is actually turning out to be a tough problem. I am looking to see if I can develop a VBA solution but this could take some time.

There are 1320 different triads you can make out of 12 people. Let's call each set of four triads a "grouping." It's not hard to generate the list of 1320 possible triads , but then you have to select groupings that are mutually exclusive and collectively exhaustive. Then on top of that you have to select more groupings that do not duplicate any of the triads in a previous grouping. I don't even know how to calculate how many that is.

Another approach is to generate all permutations of 12 people (but that is probably prohibitive since it is almost 500 million) then select the permutations that each form four unique teams.

1320 triads ??? Isn't it 220 ?

If Triad 1 contains A, B & C, then triads 2, 3 & 4 for that session cannot contain any of A, B & C

For the first triad you have a choice of 220 possible triads. For the second, 84, for the 3rd, 20 and for the 4th... just the one.

deleted - i made an error

@Glenn, i make a better solution this evening ...

I think you did make an error. There are (I'm >>>almost<<< certain) "only" 325.

15.400 ??? (column E)

I think you're getting combinations and permutations confused, maybe... For 3 names out of 12 there are 220 combinations and 1320 permutations. Here, we're looking at combinations. For the next triad, there are only 9 to choose the next 3 from, etc, etc.

I have only ever written one recursive lambda formula. This is what it needs to do.

However, I've spent enough time on the EF for one day, so I'm off now.

Originally Posted by 6StringJazzer

There are 1320 different triads you can make out of 12 people.

Originally Posted by bsalv

1320 triads ??? Isn't it 220 ?

Sorry, you are correct. There are 1320 permutations but for this application the order doesn't matter, so we care about 220 combinations.

This formula returns 4 random triads, without duplicate persons:

Formula:

Please Login or Register  to view this content.

Every time when you press F9 a new random result of 4 random thriads will be returned.

you have
for table 1 combinations(12;3) = 220 combinations
for table 2 combinations(9;3)= 84 combinations
for table 3 combinations(6;3)= 20 combinations
that makes 220*84*20 = 369.600 possibilities
but you also have the permutations of the tables = 4! = 24

369.600/24 = 15.400 = okay

i lost myself in the discussion of how many possible solutions, with the macro "Making_10_4Triads", you 'll have 10 random solutions.

Even this combination with 3 sessions is not complete unique without people paired twice.

Number 3 and 11 paired in Session 2 and 3.

24 persons and 20 sessions = 66 couples 1 time and 277 couples twice
You can change the number of persons and sessions in the macro

Say you have 15 persons = 5 triads and you want 10 sessions, in the A1:E10 those triads. Person 1 = letter A, person 15 = letter L
15 people = 105 possible couples and you want them as less as possible together.
So in the solution in the attachment, 45 couples come twice and the rest (60) one time together.
The triads for session 1 = A1:E1, the first triad = DNC = person 5 & person 14 & person 3, etc.

I think that is the answer you wanted to know, give me for a multiple of 3 persons and so many sessions a balanced solution.
The only thing, i still had to do, was the translation of all those triads to the names of the persons.
(if you agree with this method)

the posts #15-16 = 12 persons & 3 sessions = no couple twice in attachment

PS. column AA = the couples (paires) and the integer part of column AB = the number of occurencies. (decimal part was for sorting), column AC = row and column of occurency
AG1 = last 3 digits = number of couples with 1 occurency, next 3 digits = couples with 2 occurencies, etc

12 persons & 4 sessions = no couple twice

12 persons & 5 sessions = 3 couples twice

I am so confused on what the actual objective is here.

1 2 3
4 5 6
7 8 9
10 11 12

1 4 7
1 5 8
1 6 9
2 4 8

2 5 7
2 6 10
3 4 9
3 5 10

3 6 7
3 8 11
5 9 11
6 8 12

@johnnyL, i'm waiting for a response of TS. After #19, i gave more information (no repetition of a duo/couple) and i want to know if this is okay.
Your approach is looking for a repetition of a triad, you found it after 17-19 sessions, i don't think that's what TS wants.
But there is only 1 person who knows the answer.