HOW-TO make a random selection from a set of data and divide it into sub-team

[zanat0s]

Hi,

I need some help. I need to divide a group people in random teams and have these groups reformed twice per month. here is an example. Lets say i have a group of 10 people and need to divide them in random teams of 2

A
1
2
3
4
5
6
7
8
9
10

I would like a result like 1-4, 2-10, 9-5, 7-6, 8-3 Which function can give a result like that? I need combinations of people where each person cannot be in more than one team.

Is it possible in Excel?

Thank you

[dilipandey]

Hi Zanat0s,

Welcome to the forum.

You can use Random function (Rand or Randbetwen)here to pick up the names of random basis
First give serial numbers to the names and then use Random function to pick up those names to form Teams..

In case of any issues, please upload a sample file. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[zanat0s]

Dillip,

Unfortunately that doesn't solve my issue.

Why?

even if i use Randbetween some Numbers(people) might appear more than twice...

[jindon]

try

Sub test()
    Dim r As Range, i As Long, n As Long
    With CreateObject("System.Collections.SortedList")
        Randomize
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp))
            .Item(Rnd) = r.Value
        Next
        Columns(3).NumberFormat = "@"
        For i = 0 To .Count - 1 Step 2
            If i + 1 > .Count - 1 Then Exit For
            n = n + 1
            Cells(n, 3).Value = _
            .GetByIndex(i) & " - " & .GetByIndex(i + 1)
        Next
    End With
End Sub

[zanat0s]

Jindon

i will try this. Here are the exact data : 16 people 4 teams...

[jindon]

If odd number of players, one player will be left off.

And hope your Windows version is later than XP.