Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating lists of permutations
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating lists of permutations
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 "jake" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating lists of permutations
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 "barnabel" wrote: 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 "jake" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
creating lists of permutations
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:D$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:D$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:D$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:D$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:D$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 On Sun, 5 Jul 2009 07:59:01 -0700, Malcolm wrote: 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 "barnabel" wrote: 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 "jake" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Drop Down lists | Excel Worksheet Functions | |||
Creating Text lists | Excel Worksheet Functions | |||
creating lists | New Users to Excel | |||
Creating fixture lists | Excel Worksheet Functions | |||
Creating Combinations from Two Lists | Excel Discussion (Misc queries) |