Results 1 to 13 of 13
Thread: Combinations

20091104, 12:06 #1
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts
I would like to have a formula that would list all of the combinations of the following: HH, HP, ML, LP, OFF, IHT, IMBM. For example, the first would be "HH HH", the second "HH HP", the third "HH ML",etc. as well as, for example "ML HH", OFF HH","IHT IHT", etc. In essence, there would be 49 (I think that is correct) different combinations...any ideas?

20091104, 13:21 #2
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Assuming your list of combination members is in the A column the following will display all possible combination in the B column.
Code:Sub combos() Dim i As Integer, j As Integer, iRow As Integer iRow = 1 For i = 1 To 7 For j = 1 To 7 Cells(iRow, 2) = Cells(i, 1) & " " & Cells(j, 1) iRow = iRow + 1 Next j Next i End Sub

20091104, 13:42 #3
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
For a non macro solution:
With the list starting in cell A2 and the formula result starting in row 1 (any column)
Code:= OFFSET($A$2,(INT(ROW()1)/7),0) & "  " & OFFSET($A$1,MOD(ROW()+6,7)+1,0)

20091104, 14:55 #4
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts

20091104, 18:10 #5
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts

20091104, 18:29 #6
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Try this one:
Code:=OFFSET($A$1,(INT(ROW()1)/7),0) & "  " &OFFSET($A$1,ROW()7*(INT((ROW()1)/7))1,0)

20091107, 17:10 #7
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts

20091107, 17:21 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
The following macro solution assumes that column A contains only the list of strings to be used, starting in A1. In other words, all cells below the list are blank.
Code:Sub Combos() Dim i As Long Dim j As Long Dim r As Long Dim m As Long ' Last filled row in column A m = Cells(Rows.Count, 1).End(xlUp).Row r = 1 For i = 1 To m For j = 1 To m Cells(r, 2) = Cells(i, 1) & " " & Cells(j, 1) r = r + 1 Next j Next i End Sub

20091107, 17:24 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
And here is a formula solution based on the same assumption. The formula should be entered in a cell in row 1 and filled down.
=OFFSET($A$1,(INT(ROW()1)/COUNTA(A:A)),0) & "  " &OFFSET($A$1,ROW()COUNTA(A:A)*(INT((ROW()1)/COUNTA(A:A)))1,0)

20091107, 18:11 #10
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts

20091120, 10:33 #11
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts

20091120, 10:53 #12
 Join Date
 Aug 2004
 Location
 Connecticut, USA
 Posts
 816
 Thanks
 0
 Thanked 0 Times in 0 Posts
Use the formula I supplied in this post.
=OFFSET($A$1,(INT(ROW()1)/7),0) & "  " &OFFSET($A$1,ROW()7*(INT((ROW()1)/7))1,0)

20091120, 10:58 #13
 Join Date
 Jan 2001
 Posts
 1,418
 Thanks
 1
 Thanked 0 Times in 0 Posts