1. 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. 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. 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. Originally Posted by mbarron
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. Originally Posted by jlkirk
Thanks for both!
Any way we could adjust the formula to allow for the "source" data ("HH","HP", etc.) be located at A1?

6. 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. Originally Posted by mbarron
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. 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. 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. Originally Posted by HansV
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. Originally Posted by mbarron
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. Originally Posted by jlkirk
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. Originally Posted by mbarron
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
•