On Jun 19, 10:42*am, yauchildchew wrote:
Hi, I am a very beginner in excel vba. I need to generate combinations with
*repetition.
*If definition is required, the site below will be the reference. SITE

*For code below,

*CCCAAMMM
*MMAMCCCA
*etc

*will be generated.

*However, the VBA combination generator that I want to develop should only
*produce just ONE of the many repeated combinations (CCCAAMMM,MMAMCCCA,etc).

*Well, to define in a better way.
*Say, building up combinations of 4 alphabets with {A,B,C}.

*Repetition of available alphabets (ABC) is allowed so that:
*ABCA, ABBC,AAAA...
*such combinations are possible.

*Repetition of combinations with similar element/alphabet is not allowed, so
*that:
*ABCA, BCAA, AABC..... (repetition is considered to have occurred)
*there are many of them, but in my case, I only need one, anyone of them,
because
*they will produce similar result in the later stage when i sum up values from
*each of them.

*The reasons of doing this is to reduce the possible combination and increase
*efficiency of analysis.

*

Code:

Sub allup()
 *Dim a, n As Integer, c(), k As Long
 *Dim u1 As Integer, u2 As Integer, u3 As Integer
 *Dim u4 As Integer, u5 As Integer, u6 As Integer
 *Dim u7 As Integer, u8 As Integer, u9 As Integer
 *a = Array("C", "M", "U")
 *n = UBound(a) + 1
 *ReDim c(1 To Rows.Count, 1 To 9)
 *For u1 = 1 To n
 *For u2 = 1 To n
 *For u3 = 1 To n
 *For u4 = 1 To n
 *For u5 = 1 To n
 *For u6 = 1 To n
 *For u7 = 1 To n
 *For u8 = 1 To n
 *For u9 = 1 To n
 *k = k + 1
 *c(k, 9) = a(u9 - 1)
 *c(k, 8) = a(u8 - 1)
 *c(k, 7) = a(u7 - 1)
 *c(k, 6) = a(u6 - 1)
 *c(k, 5) = a(u5 - 1)
 *c(k, 4) = a(u4 - 1)
 *c(k, 3) = a(u3 - 1)
 *c(k, 2) = a(u2 - 1)
 *c(k, 1) = a(u1 - 1)
 *Next u9, u8, u7, u6, u5, u4, u3, u2, u1
 *Cells(1).Resize(k, 9) = c
 *End Sub

*Please kindly enlighten.
*Thanks.

This is based on:
http://groups.google.com/group/micro...63f96d9244c2ed

The technique is to generate ALL the combinations and then identify
and discard "repeats"

Sub BuildCombinations()
leters = Array("A", "B", "C", "D")
m = 2
For i = 0 To 3
For j = 0 To 3
For k = 0 To 3
For l = 0 To 3
Cells(m, "A").Value = leters(i) & leters(j) & leters(k) & leters(l)
m = m + 1
Next
Next
Next
Next
End Sub

This macro puts the 256 combination in column A

In B2, enter:

=internalsort(A2) and copy down

Whe

Public Function InternalSort(r As Range) As String
Dim v As String
v = r.Value
For i = 1 To (Len(v) - 1)
For j = (i + 1) To Len(v)
char_i = Mid(v, i, 1)
char_j = Mid(v, j, 1)
If Asc(char_i) Asc(char_j) Then
Mid(v, i, 1) = char_j
Mid(v, j, 1) = char_i
End If
Next j
Next i
InternalSort = v
End Function

This UDF just sorts the characters in the column A cells

In C2, enter:

=COUNTIF(B2:B$2,B2)

In A2 thru C20 we now see:

AAAA AAAA 1
AAAB AAAB 1
AAAC AAAC 1
AAAD AAAD 1
AABA AAAB 2
AABB AABB 1
AABC AABC 1
AABD AABD 1
AACA AAAC 2
AACB AABC 2
AACC AACC 1
AACD AACD 1
AADA AAAD 2
AADB AABD 2
AADC AACD 2
AADD AADD 1
ABAA AAAB 3
ABAB AABB 2
ABAC AABC 3

The first 2 in column C says that AABA is really the "same as" AAAB
If we AutoFilter column C and pick only the 1's, we have what you want.