Out of 14 numbers make all unique combinations in with only 6 numbers in the row

motilulla · Jan 7, 2019

Hello,

I got 14 numbers in cells A5:A18 these numbers are changeable every time but (but always will be the 14 numbers)

I need VBA code, which can generate all possible unique combinations with 6 numbers in the columns D:I and sum of each row combination must be 6. Is it possible?

Example data

Book1

A

B

C

D

E

F

G

H

I

J

1

2

3

4

Numbers

n1

n2

n3

n4

n5

n6

SUM

5

0

1

0

4

6

0

1

4

0

6

7

1

0

1

2

6

8

1

0

1

0

4

1

6

9

1

0

1

4

0

1

6

10

0

1

4

1

0

6

11

4

0

1

2

1

6

12

0

4

0

2

6

13

2

0

4

2

0

6

14

0

2

6

15

2

0

4

0

2

0

6

16

2

0

4

0

2

6

17

1

0

4

2

0

6

18

0

2

0

2

6

19

0

2

0

6

20

0

1

2

1

6

21

0

1

0

4

0

6

22

0

1

0

2

6

23

0

1

4

0

6

24

0

1

2

0

2

6

25

0

1

2

0

6

26

0

1

0

4

0

1

6

27

0

1

0

4

1

0

6

28

0

1

0

2

1

6

29

0

1

4

0

1

6

30

0

1

4

0

1

0

6

31

0

1

2

0

2

1

6

32

0

1

2

1

0

6

33

0

4

0

2

0

6

34

0

4

0

2

0

6

35

0

2

0

2

0

6

36

1

0

2

1

6

37

1

2

0

1

6

38

1

2

1

0

6

39

1

0

4

0

6

40

1

0

2

6

41

1

0

2

0

2

6

42

1

0

2

0

6

43

1

4

0

6

44

1

2

0

2

0

6

45

1

0

4

0

1

6

46

1

0

4

0

1

0

6

47

1

0

2

1

6

48

1

0

2

0

2

1

6

49

1

0

2

1

0

6

50

1

4

0

1

0

6

51

1

2

0

2

1

0

6

Sheet1

Thank you all

Excel 2000
Regards,
Moti

StephenCrump · Jan 9, 2019

motilulla said:
When I run the code it stop at the line below and gives the error '438'

Code:

.Resize(UBound(lCombinations) + 1).RemoveDuplicates Columns:=(ColArray), Header:=xlNo

Yes, I wasn't sure that .RemoveDuplicates with multi columns was going to work in Excel 2000.

With .AdvancedFilter instead, you can do it like this:

Code:

With Range("D5")
    .CurrentRegion.Offset(1).ClearContents
    With .Resize(UBound(lCombinations), lNoChosen + 1)
        .Value = lTargetNumbers
       .Columns(lNoChosen + 1).FormulaR1C1 = "=SUM(RC[-" & lNoChosen & "]:RC[-1])"
'            For i = 1 To .Rows.Count
'                .Rows(i).Sort .Cells(i, 1), Header:=xlNo, Orientation:=xlLeftToRight
'            Next i
        .Offset(-1).Resize(UBound(lCombinations) + 1).AdvancedFilter Action:=xlFilterInPlace, unique:=True
    .Sort Key1:=.Columns(lNoChosen + 1), Orientation:=xlTopToBottom, Order1:=xlAscending
    End With
End With

This will produce your 314 uniques.

Alternatively, if you uncomment the three .Sort lines, you'll get my 36 uniques.

StephenCrump · Jan 9, 2019

Let's look at "uniques" ...

Here's how my code generates the combinations summing to 1:

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

4

Numbers

1

2

3

6

8

10

0

1

0

5

0

1

2

3

6

8

14

0

1

0

6

0

1

2

3

6

10

14

0

1

0

7

1

2

3

8

10

14

0

1

0

8

1

2

4

6

8

10

0

1

0

9

1

2

4

6

8

14

0

1

0

10

0

1

2

4

6

10

14

0

1

0

11

4

1

2

4

8

10

14

0

1

0

12

0

1

2

5

6

8

10

0

1

0

13

2

1

2

5

6

8

14

0

1

0

14

0

1

2

5

6

10

14

0

1

0

15

2

1

2

5

8

10

14

0

1

0

16

2

1

2

6

8

10

13

0

1

17

1

2

6

8

13

14

0

1

0

18

0

1

2

6

10

13

14

0

1

0

19

1

2

8

10

13

14

0

1

0

20

1

3

6

8

10

14

0

1

0

21

1

4

6

8

10

14

0

1

0

22

1

5

6

8

10

14

0

1

0

23

1

6

8

10

13

14

0

1

0

24

2

3

6

8

10

14

0

1

0

25

2

4

6

8

10

14

0

1

0

26

2

5

6

8

10

14

0

1

0

27

2

6

8

10

13

14

0

1

0

Sheet1

For example, the first row takes the 1st, 2nd, 3rd, 6th, 8th and 10th of your numbers, i.e. 0 0 1 0 0 0

There are 24 combinations. I sort and filter these to get the one unique combination, i.e. One "1", and five "0"s.

If you filter without first sorting, the four "uniques" are:
0 1 0 0 0 0
0 0 1 0 0 0
0 0 0 0 1 0
0 0 0 0 0 1

But what does this mean, and why not also:
1 0 0 0 0 0
0 0 0 1 0 0

Purely because of the algorithm I have chosen to generate the combinations: I am assuming order doesn't matter.

If order does matter to you (?) then we should be talking permutations, not combinations.

motilulla · Jan 9, 2019

StephenCrump said:
Let's look at "uniques" ...

Here's how my code generates the combinations summing to 1:

A
B
C
D
E
F
G
H
I
J
K
L
M
N
O

<TBODY>
[TD="align: center"]4
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=00ff00]#00ff00[/URL] , align: center"]Numbers
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]9
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]10
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]11
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]4
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]12
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]2
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]17
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"]1
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]18
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]19
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]20
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]21
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]22
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]23
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

[TD="align: center"]24
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]25
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27
[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffffff]#ffffff[/URL] , align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]8
[/TD]
[TD="align: right"]10
[/TD]
[TD="align: right"]13
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]

</TBODY>

For example, the first row takes the 1st, 2nd, 3rd, 6th, 8th and 10th of your numbers, i.e. 0 0 1 0 0 0

There are 24 combinations. I sort and filter these to get the one unique combination, i.e. One "1", and five "0"s.

If you filter without first sorting, the four "uniques" are:
0 1 0 0 0 0
0 0 1 0 0 0
0 0 0 0 1 0
0 0 0 0 0 1

But what does this mean, and why not also:
1 0 0 0 0 0
0 0 0 1 0 0

Purely because of the algorithm I have chosen to generate the combinations: I am assuming order doesn't matter.

If order does matter to you (?) then we should be talking permutations, not combinations.

StephenCrump, yes I see It is wrong described I wanted a permutations actually. I am sorry you got it correct. 

Thank you very much for explaining and clarifying the difference between permutations and combinations.

Kind Regards,
Moti

motilulla · Jan 9, 2019

StephenCrump said:
Yes, I wasn't sure that .RemoveDuplicates with multi columns was going to work in Excel 2000.

With .AdvancedFilter instead, you can do it like this:

Code:

With Range("D5") .CurrentRegion.Offset(1).ClearContents With .Resize(UBound(lCombinations), lNoChosen + 1) .Value = lTargetNumbers .Columns(lNoChosen + 1).FormulaR1C1 = "=SUM(RC[-" & lNoChosen & "]:RC[-1])" ' For i = 1 To .Rows.Count ' .Rows(i).Sort .Cells(i, 1), Header:=xlNo, Orientation:=xlLeftToRight ' Next i .Offset(-1).Resize(UBound(lCombinations) + 1).AdvancedFilter Action:=xlFilterInPlace, unique:=True .Sort Key1:=.Columns(lNoChosen + 1), Orientation:=xlTopToBottom, Order1:=xlAscending End With End With

This will produce your 314 uniques.

Alternatively, if you uncomment the three .Sort lines, you'll get my 36 uniques.

StephenCrump, I did change as per your instructions but it is not filtering generating all 3003 permutations, neither even if I uncomment the three. Sort lines; do not get your 36 uniques.

If I re use the line I added it filter 314 but do not eliminate the false

Please can you take a look?

Thank you for your help

Kind Regards,
Moti

StephenCrump · Jan 9, 2019

motilulla said:
StephenCrump, yes I see It is wrong described I wanted a permutations actually.

Do you want code for unique permutations?

Unique permutations adding to 1, for example, would be:
1 0 0 0 0 0
0 1 0 0 0 0
0 0 1 0 0 0
0 0 0 1 0 0
0 0 0 0 1 0
0 0 0 0 0 1

StephenCrump · Jan 9, 2019

motilulla said:
I did change as per your instructions but it is not filtering generating all 3003 permutations, neither even if I uncomment the three. Sort lines; do not get your 36 uniques.

If I re use the line I added it filter 314 but do not eliminate the false

Code:

'Your line:
Range("D4:I3007").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'My Line
.Offset(-1).Resize(UBound(lCombinations) + 1).AdvancedFilter Action:=xlFilterInPlace, Unique:=True

.Offset(-1).Resize(UBound(lCombinations) + 1) is the range D4:J3007, i.e. same as yours, but with one extra column to allow for the sum column.

So my generalised code should behave exactly the same as your code with the hard-coded range, i.e. it should filter the 3,003 total combinations to show your 314 "uniques".

If you include my three .Sort lines, the code should show only the 36 true uniques.

motilulla · Jan 10, 2019

StephenCrump said:
Do you want code for unique permutations?

Unique permutations adding to 1, for example, would be:
1 0 0 0 0 0
0 1 0 0 0 0
0 0 1 0 0 0
0 0 0 1 0 0
0 0 0 0 1 0
0 0 0 0 0 1

StephenCrump, I think yes that is want I am looking unique permutations 314

motilulla · Jan 10, 2019

StephenCrump said:
Code:

'Your line: Range("D4:I3007").AdvancedFilter Action:=xlFilterInPlace, Unique:=True 'My Line .Offset(-1).Resize(UBound(lCombinations) + 1).AdvancedFilter Action:=xlFilterInPlace, Unique:=True

.Offset(-1).Resize(UBound(lCombinations) + 1) is the range D4:J3007, i.e. same as yours, but with one extra column to allow for the sum column.

So my generalised code should behave exactly the same as your code with the hard-coded range, i.e. it should filter the 3,003 total combinations to show your 314 "uniques".

If you include my three .Sort lines, the code should show only the 36 true uniques.

StephenCrump, theatrically you are correct it must work but unfortunately what I am doing wrong cannot trace it out. I create a new sheet cleaned up code but no luck there is some parameters which may be Excel 2000 is not accepting as you are using latest version may some one has the Excel2000 yet can try it and conform what is the real problem. 

I really appreciate a lot you, that you are willing to solve it but it is my older version problem.

Thank you so much for your help and time 

Kind Regards, 
Moti

StephenCrump · Jan 12, 2019

motilulla said:
I think yes that is want I am looking unique permutations

Code:

Dim k As Long, row As Long
Dim N As Variant, Permutations() As Variant
Sub Test()
    
    Dim Permutation() As Variant
    Dim rng As Range
    
    Set rng = Range("A5:B8")
    N = rng.Value
    k = 6
    row = 0
    ReDim Permutation(1 To k)
    ReDim Permutations(1 To Application.Permut(Application.Sum(rng.Columns(2)), k), 1 To k)
        
    Call GetPermutations(Permutation, 1)
    
    With Range("D5")
        .CurrentRegion.Offset(1).ClearContents
        With .Resize(row, k + 1)
            .Value = Permutations
           .Columns(k + 1).FormulaR1C1 = "=SUM(RC[-" & k & "]:RC[-1])"
            .Sort Key1:=.Columns(k + 1), Orientation:=xlTopToBottom, Order1:=xlAscending
        End With
    End With
        
End Sub
Sub GetPermutations(ByVal Permutation As Variant, col As Long)
    
    Dim i As Long, j As Long
    
    For i = 1 To UBound(N)
        If N(i, 2) > 0 Then
            Permutation(col) = N(i, 1)
            If col < k Then
                N(i, 2) = N(i, 2) - 1
                Call GetPermutations(Permutation, col + 1)
                N(i, 2) = N(i, 2) + 1
            Else
                row = row + 1
                For j = 1 To k
                    Permutations(row, j) = Permutation(j)
                Next j
            End If
        End If
    Next i
       
End Sub

Book1

A

B

C

D

E

F

G

H

I

J

4

Number

Freq

n1

n2

n3

n4

n5

n6

SUM

5

0

6

0

6

1

4

0

1

7

2

3

0

1

0

1

8

4

1

0

1

0

1

9

0

1

0

1

10

0

1

0

1

11

1

0

1

12

0

2

13

0

1

2

14

0

2

0

2

15

0

1

0

1

2

16

0

1

0

2

17

0

2

0

2

18

0

1

0

1

2

19

0

1

0

1

0

2

20

0

1

0

2

21

0

2

0

2

22

0

1

0

1

2

23

0

1

0

1

0

2

24

0

1

0

1

0

2

25

0

1

0

2

26

etc

27

4

2

1

2

1

2

12

28

4

2

1

2

1

12

29

4

2

1

2

12

30

4

2

1

2

1

12

31

4

2

1

12

Sheet1

motilulla · Jan 12, 2019

StephenCrump said:
Code:

Dim k As Long, row As Long Dim N As Variant, Permutations() As Variant Sub Test() Dim Permutation() As Variant Dim rng As Range Set rng = Range("A5:B8") N = rng.Value [COLOR=#000000]k = 6 [/COLOR] row = 0 ReDim Permutation(1 To k) ReDim Permutations(1 To Application.Permut(Application.Sum(rng.Columns(2)), k), 1 To k) Call GetPermutations(Permutation, 1) With Range("D5") .CurrentRegion.Offset(1).ClearContents With .Resize(row, k + 1) .Value = Permutations .Columns(k + 1).FormulaR1C1 = "=SUM(RC[-" & k & "]:RC[-1])" .Sort Key1:=.Columns(k + 1), Orientation:=xlTopToBottom, Order1:=xlAscending End With End With End Sub Sub GetPermutations(ByVal Permutation As Variant, col As Long) Dim i As Long, j As Long For i = 1 To UBound(N) If N(i, 2) > 0 Then Permutation(col) = N(i, 1) If col < k Then N(i, 2) = N(i, 2) - 1 Call GetPermutations(Permutation, col + 1) N(i, 2) = N(i, 2) + 1 Else row = row + 1 For j = 1 To k Permutations(row, j) = Permutation(j) Next j End If End If Next i End Sub

A
B
C
D
E
F
G
H
I
J

<TBODY>
[TD="align: center"]4
[/TD]
[TD="align: center"]Number
[/TD]
[TD="align: center"]Freq
[/TD]
[TD="align: center"][/TD]
[TD="align: right"]n1
[/TD]
[TD="align: right"]n2
[/TD]
[TD="align: right"]n3
[/TD]
[TD="align: right"]n4
[/TD]
[TD="align: right"]n5
[/TD]
[TD="align: right"]n6
[/TD]
[TD="align: center"]SUM
[/TD]

[TD="align: center"]5
[/TD]
[TD="align: center"][/TD]
[TD="align: center"]6
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]7
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]8
[/TD]
[TD="align: center"]4
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]9
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]10
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]11
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]

[TD="align: center"]12
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]13
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]14
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]15
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]16
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]17
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]18
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]19
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]20
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]21
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]22
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]23
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]24
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]25
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]

[TD="align: center"]26
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"]etc …
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]12
[/TD]

[TD="align: center"]28
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]12
[/TD]

[TD="align: center"]29
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]12
[/TD]

[TD="align: center"]30
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]12
[/TD]

[TD="align: center"]31
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]12
[/TD]

</TBODY>

StephenCrump,

ray: Hats off to you very intelligently done amazing idea separating the unique numbers from the string and counting the frequency of each unique number you really did the best. I checked carefully and getting all the possible matches, I find all are there in the unique permutations created by your code.

I am very glad with your kind help, thank you for your time you spent to solve this difficult task.

Have a great weekend and Good Luck

Kind Regards, 
Moti

This is just to clearly k = 6, I think this mean create permutations by 6 positions if I change k = 5, k = 4, k = 3 or k = 2 the code create permutations by the positions of k numbers. Why when I change K = 7, 8, 9 (I mean greeter than value 6) I get message "Run time error 7: out of memory"

Rich (BB code):

</SPAN></SPAN>
  Set rng = Range("A5:B8")</SPAN></SPAN>
    N = rng.Value</SPAN></SPAN>
    k = 6</SPAN></SPAN>  
    row = 0</SPAN></SPAN>
    ReDim Permutation(1 To k)</SPAN></SPAN>
    ReDim Permutations(1 To Application.Permut(Application.Sum(rng.Columns(2)), k), 1 To k)
</SPAN></SPAN>

Out of 14 numbers make all unique combinations in with only 6 numbers in the row

motilulla

Well-known Member

StephenCrump

MrExcel MVP

Excel Facts

StephenCrump

MrExcel MVP

motilulla

Well-known Member

motilulla

Well-known Member

StephenCrump

MrExcel MVP

StephenCrump

MrExcel MVP

motilulla

Well-known Member

motilulla

Well-known Member

StephenCrump

MrExcel MVP

motilulla

Well-known Member

Similar threads

Forum statistics

Share this page

Out of 14 numbers make all unique combinations in with only 6 numbers in the row

Well-known Member

MrExcel MVP

Excel Facts

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock