list for unique combinations 1

[wbalbion68]

Hi everyone,
I run a mini league of ten teams. At the end of the season three teaams will be relegated. I need to list the possibilities of which three will be relegated. Thanks

 

[macropod]

Hi wbalbion68,

With no details of what application you're using, how the data are structured and no indication of what rules determine relegation, don't you suppose you're expecting rather much in the way of help.

At this stage, all we could say is use whatever application suits your needs for typing (and maybe printing) a list of three names.

Cheers
Paul Edstein
[MS MVP - Word]

 

[wbalbion68]

Hi Macropod,

Sorry to be so vague. I will be using excel. the structure is as follows:

Team Possible 1 Possble 2 Possible 3
A A B C
A B D
A B E
ETC
A C D
ETC
B B A D
C B A E
D B A F
ETC
E
F
G
H
I
J

The three bottom teams will be relgated. I want a list of every possibility.Hope I have made it clear this time

Thnks for any help

 

[macropod]

Hi wbalbion68,

All that your list appears to be is a listing of all the team combinations in groups of three. I don't see how one can get a relegations list from that. Still, if that's all you want, try the following macro:

Sub MakeList()
Dim i As Long, j As Long, k As Long, l As Long
For i = 1 To 10
  For j = i + 1 To 10
    For k = j + 1 To 10
      l = l + 1
      ActiveSheet.Cells(l, 1).Value = Chr(64 + i)
      ActiveSheet.Cells(l, 2).Value = Chr(64 + j)
      ActiveSheet.Cells(l, 3).Value = Chr(64 + k)
    Next
  Next
Next
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[Gruuuu]

Another option, to get a unique list, is to take your games list, make sure it's one long table of all game possibilities, and run an Advanced Filter (Unique) (Copy to Range)
It will return a unique list of all combinations.

 

[strongm]

>For i = 1 To 10
> For j = i + 1 To 10
> For k = j + 1 To 10

I think the following might be better

For i = 1 To 8
For j = i + 1 To 9
For k = j + 1 To 10

 

[Gruuuu]

That would also work, assuming the team names are 'A', 'B', and 'C'.

 

[macropod]

I think the following might be better

For i = 1 To 8
For j = i + 1 To 9
For k = j + 1 To 10

I'm nost sure it's 'better' in terms of output, in that it gives exactly the same result. It is a more accurate reflection of what the code actually does, though.

Cheers
Paul Edstein
[MS MVP - Word]

 

[wbalbion68]

Thanks to everyone for replying. I have now got what I need, with all your help. Thanks again