how can i get a list of combinations?

  • Thread starter Thread starter Pastor Pudge
  • Start date Start date
P

Pastor Pudge

I would like to use excel to come up with a list of all combinations of 3,
from a list of 5 items. so for the 5 items, I am looking for all possible
combinations of 3. any ideas of how excel could help me with this so I don't
have to figure it out?

Thanks and Merry Christmas.
 
I would like to use excel to come up with a list of all combinations of 3,
from a list of 5 items.  so for the 5 items, I am looking for all possible
combinations of 3.  any ideas of how excel could help me with this so Idon't
have to figure it out?

Thanks and Merry Christmas.
Click to expand...

Hi,

This will wotk for any number of elements. It takes imput from column
A on sheet 1 and outputs to sheet 2.

Sub ComBinAtions()
Dim MyRange As Range
Dim OutPutSheet As Worksheet
Dim a As Long, b As Long, c As Long
Dim Elements As Long
lastrow = Sheet1.Cells(Rows.Count, "A").End(xlUp).Row
Set MyRange = Sheet1.Range("A1:A" & lastrow)
Set OutPutSheet = Sheet2
Elements = MyRange.Cells.Count
For a = 1 To Elements - 2
For b = a + 1 To Elements - 1
For c = b + 1 To Elements
OutPutSheet.Cells(Rows.Count, 1).End(xlUp)(2, 1).Value = _
MyRange(a) & ", " & MyRange(b) & ", " & MyRange(c)
Next c
Next b
Next a
End Sub

Mike
 
Back
Top