creating lists of permutations

[Guest]

I have a list of all the football teams in a league, like so:

Dundee United
Partick Thistle
Queen of the South

etc.

I need to generate a list of fixtures for the coming football season, like so:

Dundee United Partick Thistle
Dundee United Queen of the South
Partick Thistle Quen of the South

etc.

Any ideas how to do this without manually manipulating huge amounts of data
(sometimes I have to do this with HUGE lists and it becomes very time
consuming)

Cheers,

Jake

 

[Guest]

Here is a little macro that may help you. you can set which column has the
names in it and which column to put the reult in. Don't make them the same

Peter Richardson
Sub leagueMatch()
Dim i, j As Long
Dim datacolumn As String
Dim resultColumn As String
Dim firstRow, lastRow, currRow As Long

datacolumn = "A"
resultColumn = "B"
firstRow = 1
currRow = 1
lastRow = Range(datacolumn & Rows.Count).End(xlUp).Row

For i = firstRow To lastRow - 1
For j = i + 1 To lastRow
Range(resultColumn & Format(currRow)) = Range(datacolumn &
Format(i)) & " vs " & Range(datacolumn & Format(j))
currRow = currRow + 1
Next
Next
End Sub

 

[Malcolm]

Peter, that macro looks perfect for what he wanted to do.

I wonder if you could help me figure out how to write a similar macro? In my
case, instead of one list of teams, I have six short lists of attributes and
I need to create a list of all possible permutations.

I have a matrix, 6 columns x 2 rows, which contains letter codes. Example:

A C E R Q L
B D K S P N

Each column is a choice; that is, either A or B; either C or D, etc.

There are 64 permutations, e.g.
ACERQL
ACERQN
ACERPL
ACERPN
etc.

The letters in my 12 cells sometimes change based on formulas elsewhere in
the document, so the list needs to change with them.

To make things even more complicated, some columns will sometimes have THREE
choices in them instead of two (again, changing based on formulas), so the
ideal answer would enable me to deal with those cases automatically.

Cheers,
Malcolm

 

[Lars-Åke Aspelin]

If you would like to try a worksheet function to produce the list of
permutations you may try this:

Assuming you matrix is in cell A1 to F3 (1 to 3 values per column)

Put the following formula in cell G1 and copy down to cell G729.
729 (3^6) is the number of permutations if you have 3 options in each
of the 6 columns.

=IF(ROW()<=(COUNTA(A$1:A$3)*COUNTA(B$1:B$3)*COUNTA(C$1:C$3)*
COUNTA(D$1$3)*COUNTA(E$1:E$3)*COUNTA(F$1:F$3)),
OFFSET(A$1,MOD(INT((ROW()-1)/(COUNTA(B$1:B$3)*COUNTA(C$1:C$3)*

COUNTA(D$1$3)*COUNTA(E$1:E$3)*COUNTA(F$1:F$3))),COUNTA(A$1:A$3)),0)&
OFFSET(A$1,MOD(INT((ROW()-1)/(COUNTA(C$1:C$3)*COUNTA(D$1$3)*
COUNTA(E$1:E$3)*COUNTA(F$1:F$3))),COUNTA(B$1:B$3)),1)&
OFFSET(A$1,MOD(INT((ROW()-1)/(COUNTA(D$1$3)*COUNTA(E$1:E$3)*
COUNTA(F$1:F$3))),COUNTA(C$1:C$3)),2)&
OFFSET(A$1,MOD(INT((ROW()-1)/(COUNTA(E$1:E$3)*COUNTA(F$1:F$3))),
COUNTA(D$1$3)),3)&OFFSET(A$1,MOD(INT((ROW()-1)/(COUNTA(F$1:F$3))),

COUNTA(E$1:E$3)),4)&OFFSET(A$1,MOD(INT((ROW()-1)/1),COUNTA(F$1:F$3)),5),"")

Maybe someone can replace this with some nice, and shorter, array
formula.

Hope this helps / Lars-Åke