Data mix and listing
 

Option Explicit

Sub tester9()
Dim arr(0 To 9) As Long
Dim arr1(1 To 3) As Long
Dim cnt As Long, varr As Variant
Dim j As Long, i As Long, k As Long
Dim m As Long
'Dim sStr as String
varr = Range("C10:L10")
arr1(1) = Range("M10").Value
j = 70
Range("C70").CurrentRegion.ClearContents
For i = 1 To 1023
bldArr i, arr, cnt
If cnt = 2 Then

m = 2
' sStr = ""
For k = 0 To 9
' sStr = sStr & arr(k)
If arr(k) = 1 Then
If varr(1, k + 1) = 0 Then
Exit For
End If
arr1(m) = varr(1, k + 1)
m = m + 1
End If
If m = 4 Then Exit For
Next
' Debug.Print i, sStr, m
If m = 4 Then
Cells(j, 3).Resize(1, 3).Value = arr1
j = j + 1
End If
End If
Next

End Sub

Sub bldArr(num As Long, arr() As Long, cnt As Long)
Dim lNum As Long, i As Long
lNum = num
Dim sStr As String
sStr = ""
cnt = 0
For i = 9 To 0 Step -1
If lNum And 2 ^ i Then
cnt = cnt + 1
arr(9 - i) = 1
sStr = sStr & "1"
Else
arr(9 - i) = 0
sStr = sStr & "0"
End If
Next
' If cnt = 2 Then
' Debug.Print num, sStr
' End If
End Sub

--
Regards,
Tom Ogilvy

"Rick" wrote in message
...
Help required please.

IN cell M5, I enter a number.
In cell range C10:G10 I enter other numbers all different
from the number in M5. Sometimes there are empty cells in
the this range (C10:L10).

e.g M5 = 24
C10=27
D10=52
E10=9
F10=63
G10=empty
H to L=empty

Using a macro how can I list in three columns starting
from C70 to E+, all the combinations of these numbers with
the number in M5 as a common number in all the
combinations. The (common) number entered in M5 is always
in column C.

C D E
R70 24 27 52
71 24 27 9
72 24 27 63
73 24 52 27
74 24 52 9
75 24 52 63
76 24 9 27
77 24 9 52
and continuing until all combinations are listed.

Later, as I develop my spreadsheet further, I can see a
need to also have two other macro's to basically do the
same thing as above, but the common number (M5) will be
exclusively in column D and the macro should then find all
combinations and list them in C70 to E+. The third macro
will do the same but have the common number exclusively in
column D and calc all the combinations. If anyone can help
with the first macro, could I just alter some of the code
to achieve the next two solutions. If so, could you give
me some thoughts on what to alter in the macro.

Thankyou in advance.
Regards

Data mix and listing
 

Thanks Tom,
Regards,
Rick

-----Original Message-----
Option Explicit

Sub tester9()
Dim arr(0 To 9) As Long
Dim arr1(1 To 3) As Long
Dim cnt As Long, varr As Variant
Dim j As Long, i As Long, k As Long
Dim m As Long
'Dim sStr as String
varr = Range("C10:L10")
arr1(1) = Range("M10").Value
j = 70
Range("C70").CurrentRegion.ClearContents
For i = 1 To 1023
bldArr i, arr, cnt
If cnt = 2 Then

m = 2
' sStr = ""
For k = 0 To 9
' sStr = sStr & arr(k)
If arr(k) = 1 Then
If varr(1, k + 1) = 0 Then
Exit For
End If
arr1(m) = varr(1, k + 1)
m = m + 1
End If
If m = 4 Then Exit For
Next
' Debug.Print i, sStr, m
If m = 4 Then
Cells(j, 3).Resize(1, 3).Value = arr1
j = j + 1
End If
End If
Next

End Sub

Sub bldArr(num As Long, arr() As Long, cnt As Long)
Dim lNum As Long, i As Long
lNum = num
Dim sStr As String
sStr = ""
cnt = 0
For i = 9 To 0 Step -1
If lNum And 2 ^ i Then
cnt = cnt + 1
arr(9 - i) = 1
sStr = sStr & "1"
Else
arr(9 - i) = 0
sStr = sStr & "0"
End If
Next
' If cnt = 2 Then
' Debug.Print num, sStr
' End If
End Sub

--
Regards,
Tom Ogilvy

"Rick" wrote in message
...
Help required please.

IN cell M5, I enter a number.
In cell range C10:G10 I enter other numbers all
different
from the number in M5. Sometimes there are empty cells
in
the this range (C10:L10).

e.g M5 = 24
C10=27
D10=52
E10=9
F10=63
G10=empty
H to L=empty

Using a macro how can I list in three columns starting
from C70 to E+, all the combinations of these numbers
with
the number in M5 as a common number in all the
combinations. The (common) number entered in M5 is
always
in column C.

C D E
R70 24 27 52
71 24 27 9
72 24 27 63
73 24 52 27
74 24 52 9
75 24 52 63
76 24 9 27
77 24 9 52
and continuing until all combinations are listed.

Later, as I develop my spreadsheet further, I can see a
need to also have two other macro's to basically do the
same thing as above, but the common number (M5) will be
exclusively in column D and the macro should then find
all
combinations and list them in C70 to E+. The third macro
will do the same but have the common number exclusively
in
column D and calc all the combinations. If anyone can
help
with the first macro, could I just alter some of the
code
to achieve the next two solutions. If so, could you give
me some thoughts on what to alter in the macro.

Thankyou in advance.
Regards



.

Data mix and listing
 

Hi Tom,
Could you have a look at the code.
If I enter 8 in M10 and
2, 4, 5, 6, 7, 9 in C10, D10, E10, F10, G10, H10
respectively, the code runs without error, but some of the
combinations are missing. For example:-
8,7,2
8,7,4
8,7,5
8,9,2
8,7,6
8,6,2
8,6,5
8,6,4
8,9,4 etc etc

This is what I get
8 7 9
8 6 9
8 6 7
8 5 9
8 5 7
8 5 6
8 4 9
8 4 7
8 4 6
8 4 5
8 2 9
8 2 7
8 2 6
8 2 5
8 2 4


Also, what change do I need to do to the code to have the
value in M10 appear in the second column only with all the
combinations and also to have the value in M10 appear
exclusively in the third column with all the other
combinations. I will use 3 separate macros.

Cheers
Rick


-----Original Message-----
Option Explicit

Sub tester9()
Dim arr(0 To 9) As Long
Dim arr1(1 To 3) As Long
Dim cnt As Long, varr As Variant
Dim j As Long, i As Long, k As Long
Dim m As Long
'Dim sStr as String
varr = Range("C10:L10")
arr1(1) = Range("M10").Value
j = 70
Range("C70").CurrentRegion.ClearContents
For i = 1 To 1023
bldArr i, arr, cnt
If cnt = 2 Then

m = 2
' sStr = ""
For k = 0 To 9
' sStr = sStr & arr(k)
If arr(k) = 1 Then
If varr(1, k + 1) = 0 Then
Exit For
End If
arr1(m) = varr(1, k + 1)
m = m + 1
End If
If m = 4 Then Exit For
Next
' Debug.Print i, sStr, m
If m = 4 Then
Cells(j, 3).Resize(1, 3).Value = arr1
j = j + 1
End If
End If
Next

End Sub

Sub bldArr(num As Long, arr() As Long, cnt As Long)
Dim lNum As Long, i As Long
lNum = num
Dim sStr As String
sStr = ""
cnt = 0
For i = 9 To 0 Step -1
If lNum And 2 ^ i Then
cnt = cnt + 1
arr(9 - i) = 1
sStr = sStr & "1"
Else
arr(9 - i) = 0
sStr = sStr & "0"
End If
Next
' If cnt = 2 Then
' Debug.Print num, sStr
' End If
End Sub

--
Regards,
Tom Ogilvy

"Rick" wrote in message
...
Help required please.

IN cell M5, I enter a number.
In cell range C10:G10 I enter other numbers all
different
from the number in M5. Sometimes there are empty cells
in
the this range (C10:L10).

e.g M5 = 24
C10=27
D10=52
E10=9
F10=63
G10=empty
H to L=empty

Using a macro how can I list in three columns starting
from C70 to E+, all the combinations of these numbers
with
the number in M5 as a common number in all the
combinations. The (common) number entered in M5 is
always
in column C.

C D E
R70 24 27 52
71 24 27 9
72 24 27 63
73 24 52 27
74 24 52 9
75 24 52 63
76 24 9 27
77 24 9 52
and continuing until all combinations are listed.

Later, as I develop my spreadsheet further, I can see a
need to also have two other macro's to basically do the
same thing as above, but the common number (M5) will be
exclusively in column D and the macro should then find
all
combinations and list them in C70 to E+. The third macro
will do the same but have the common number exclusively
in
column D and calc all the combinations. If anyone can
help
with the first macro, could I just alter some of the
code
to achieve the next two solutions. If so, could you give
me some thoughts on what to alter in the macro.

Thankyou in advance.
Regards



.