Combining cells in a "simple combination" fashion

Question

I've looked for a way to do this in several forums and websites, but found no clue... Hope someone can help me!

Here is a sample of the data I have:

My Data

I want an arrangement of these cells, outputting pairs that reflect how many times a particular student interacted in these groups. In other words, it is a simple combination. For each group,

there are n!/p!*(n-p)! possibilities of interaction,
where "n" is the number of the students in each group (in the sample, ranging from 2 to 4)
and "p" equals 2 (pairs of interaction).

Note that each array of students has a different number of students.

Thus, the output would be something like this:

It is my first post, but I hope I made it clear enough.

Guys, run the code snippet, it will show what his list looks like. Remember he said it's his first post.... — Davesexcel, Jan 09 '18 at 15:01
Possible duplicate of [Expanding column cells for each column cell](https://stackoverflow.com/questions/31472816/expanding-column-cells-for-each-column-cell/31594569#31594569) — , Jan 09 '18 at 15:05

Vityata · Answer 1 · 2018-01-09T15:36:48.023

Disclaimer: It is really a good idea to read the rules in StackOverflow concerning asking a question and to give some code, when you are trying to solve a problem. StackOverflow is not a free coding service and etc.

Howerver, at least you took screenshots, which is something. In a way. As a next step try to pass the values of the Excel cells to arrays or lists (whatever you want). In my case, I have hardcoded them as group1 and group4. The groups are consistent of numbers and not of names, because it is a bit faster this way and more understandable. In your example you may consider remapping them back at the end:

Public Sub TestMe()

    Dim group1, group4
    Dim groupOfAll, group
    Dim student
    Dim cnt         As Long
    Dim cnt2        As Long

    group1 = Array(1, 2, 3)
    group4 = Array(4, 5, 6, 7)
    groupOfAll = Array(group1, group4)

    For Each group In groupOfAll
        cnt2 = 1
        For Each student In group
            For cnt = LBound(group) + cnt2 To UBound(group)
                Debug.Print student; "<>"; group(cnt)
            Next cnt
            cnt2 = cnt2 + 1
        Next student
        Debug.Print "----party------"
    Next group

End Sub

The tricky part in the code is that you loop every person per group, but after you finish with the first person you do not loop through him again. This is achieved with For cnt = LBound(group) + cnt2 To UBound(group) and the cnt2=cnt2+1. cnt2 = 1, because we start with the first element from the Array in position 0, and we "introduce" this student to the next one. This is the result:

 1 <> 2 
 1 <> 3 
 2 <> 3 
----party------
 4 <> 5 
 4 <> 6 
 4 <> 7 
 5 <> 6 
 5 <> 7 
 6 <> 7 
----party------

score -1 · Answer 2 · answered Feb 14 '18 at 17:58

Unfortunately, I didn't manage to code this in VBA. Rather, I did it with Python. Here is the code, in case anyone need this too:

from itertools import combinations

groups = open("C:\file.csv", "r")
students = groups.readlines()
for line in students:
    students2 = line.split(",")
    students3 = list(combinations(students2, 2))
    for edges in students3:
        edges2 = str(edges)
        print(edges2)

groups.close()

Thanks everybody anyway.

Combining cells in a "simple combination" fashion

2 Answers2