data permutation / combination

  • Thread starter Thread starter hamoneye
  • Start date Start date
H

hamoneye

Hi
i need to find a way to permute my data in an excel sheet.
I have 5 different group of data which i need to permute with.
for example
rate diame material surface schedule
150 1/2 321 rf 10
300 3/4 304 ff 40
600 1 316 rj xs
900 1-1/4 105 80
1500 1-1/2 alloy 20 160
2500 2 ens s32720 xxs
2-1/2
3
3-1/2
4
4-1/2
5
5-1/2
6

well for diame 1, such as for all the values, i must have all the
combination/permutation between the other group.
I hope i was able to let yo understood.
Thank to all
 
With your data table starting in cell A1, with labels in row 1, and all possible values in the
columns (without blanks between), run the macro below.

HTH,
Bernie
MS Excel MVP

Sub ListCombins()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer

Dim iMax As Integer
Dim jMax As Integer
Dim kMax As Integer
Dim lMax As Integer
Dim mMax As Integer

Dim n As Long

iMax = Cells(Rows.Count, 1).End(xlUp).Row
jMax = Cells(Rows.Count, 2).End(xlUp).Row
kMax = Cells(Rows.Count, 3).End(xlUp).Row
lMax = Cells(Rows.Count, 4).End(xlUp).Row
mMax = Cells(Rows.Count, 5).End(xlUp).Row

n = Application.WorksheetFunction.Max(iMax, jMax, kMax, lMax, mMax) + 3

If (iMax - 1) * (jMax - 1) * (kMax - 1) * (lMax - 1) * (mMax - 1) + n > Rows.Count Then
MsgBox "Too many combinations"
Exit Sub
End If

For i = 2 To iMax
For j = 2 To jMax
For k = 2 To kMax
For l = 2 To lMax
For m = 2 To mMax
Cells(n, 1).Value = Cells(i, 1).Value
Cells(n, 2).Value = Cells(j, 2).Value
Cells(n, 3).Value = Cells(k, 3).Value
Cells(n, 4).Value = Cells(l, 4).Value
Cells(n, 5).Value = Cells(m, 5).Value
n = n + 1
Next m
Next l
Next k
Next j
Next i
End Sub
 
With your data table starting in cell A1, with labels in row 1, and all possible values in the
columns (without blanks between), run the macro below.

HTH,
Bernie
MS Excel MVP

Sub ListCombins()
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim m As Integer

Dim iMax As Integer
Dim jMax As Integer
Dim kMax As Integer
Dim lMax As Integer
Dim mMax As Integer

Dim n As Long

iMax = Cells(Rows.Count, 1).End(xlUp).Row
jMax = Cells(Rows.Count, 2).End(xlUp).Row
kMax = Cells(Rows.Count, 3).End(xlUp).Row
lMax = Cells(Rows.Count, 4).End(xlUp).Row
mMax = Cells(Rows.Count, 5).End(xlUp).Row

n = Application.WorksheetFunction.Max(iMax, jMax, kMax, lMax, mMax) + 3

If (iMax - 1) * (jMax - 1) * (kMax - 1) * (lMax - 1) * (mMax - 1) + n > Rows.Count Then
MsgBox "Too many combinations"
Exit Sub
End If

For i = 2 To iMax
For j = 2 To jMax
For k = 2 To kMax
For l = 2 To lMax
For m = 2 To mMax
Cells(n, 1).Value = Cells(i, 1).Value
Cells(n, 2).Value = Cells(j, 2).Value
Cells(n, 3).Value = Cells(k, 3).Value
Cells(n, 4).Value = Cells(l, 4).Value
Cells(n, 5).Value = Cells(m, 5).Value
n = n + 1
Next m
Next l
Next k
Next j
Next i
End Sub
Click to expand...

Thank man
you rule!
you save me from an huge past and copy action .
Thank you very much it works great
 
Back
Top