Results 1 to 13 of 13

Thread: Combinations

  1. #1
    Bronze Lounger
    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?

  2. #2
    5 Star Lounger
    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

  3. #3
    5 Star Lounger
    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)

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mbarron View Post
    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)
    Thanks for both!

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jlkirk View Post
    Thanks for both!
    Any way we could adjust the formula to allow for the "source" data ("HH","HP", etc.) be located at A1?

  6. #6
    5 Star Lounger
    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)
    I wanted to do this earlier but wasn't couldn't get my head around it at that time.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mbarron View Post
    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)
    Thanks again, MBarron. Is there a way to change either the macro or formula so as to allow for a range of cells instead of just seven? For example, if I had a range of A1-A12, or A1-A15, etc. I'm thinking to make it a bit more versatle. Thanks.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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)


  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    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)
    Thanks Hans.

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mbarron View Post
    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)
    MBarron,
    How would I change the formula if my source data began in A1, and I wanted the combinations to begin in B1?
    Thanks again.

  12. #12
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by jlkirk View Post
    MBarron,
    How would I change the formula if my source data began in A1, and I wanted the combinations to begin in B1?
    Thanks again.
    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)

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by mbarron View Post
    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)

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •