Problem with Looping and Output
 

I posted to the message board a few days ago but have seen message
hence I post again.

The code below is designed to compare two identical blocks in ranges
A:C120 and F1:H120.

Each time it places a set in the output area (Starting in row K1:P1)it
must check from the first row down to see if there are any duplicates
that must not be place in the output area. However the code as is
produces duplicates. I am seking assitance to have this rectified. I
feel like I almost have it but just Missing something.
Any help is greatly appreciated.
Sub MixNumbers()
Dim holdNum(1 To 6) As Integer
Dim NumCount As Long
Dim r As Long
Dim T As Long
Dim n As Range
Dim S As Range
Dim d As Integer
Dim v As Variant
Dim x As Variant
Dim y As Range
Dim m As Long
Dim z As Integer
Dim rn As Long
Dim c As Long

rn = 1
c = 0

NumCount = 0
'Clear the target area
Range("Y2:Y5,S2:X2,K1:P65500").ClearContents
Application.StatusBar = ""

For T = 1 To 120
For r = 1 To 120
For Each n In Range(Cells(r, 1), Cells(r, 3))
For Each S In Range(Cells(T, 6), Cells(T, 8))
If S = n Then
c = c + 1
End If
Next S
Next n

If c = 0 Then
For Each v In Union(Range(Cells(T, 6), Cells(T,
8)), Range(Cells(r, 1), Cells(r, 3)))
d = d + 1
holdNum(d) = v
Next v
d = 0
'Temporarily place values in array on worksheet
Range(Cells(2, 17), Cells(2, 22)).Value =
holdNum()
'Check existing combination to see if there are
any matches
For m = 1 To NumCount
For Each y In Range(Cells(m, 11), Cells(m,
16))
For Each x In holdNum()
If y = x Then
z = z + 1
End If
' A exact match is found
If z = 4 Then
z = 0
Exit For
Else
End If
Next x
Next y
Next m
'Place value of array to target area on
worksheet
If z < 4 Then
Range(Cells(rn, 11), Cells(rn,
16)).Value = holdNum()
NumCount = NumCount + 1
Application.StatusBar = "Sets
Processed: " & Format(NumCount, ("#,##0#"))
rn = rn + 1
End If

End If ' IF C = 0
c = 0
Next r
Next T
'application.StatusBar = ""
End Sub

Problem with Looping and Output
 

DNK,

It would help if you posted a small example of what your input and output
looks like, and how you want to determine 'duplicates'.

HTH,
Bernie
MS Excel MVP

"Dnk" wrote in message
om...
I posted to the message board a few days ago but have seen message
hence I post again.

The code below is designed to compare two identical blocks in ranges
A:C120 and F1:H120.

Each time it places a set in the output area (Starting in row K1:P1)it
must check from the first row down to see if there are any duplicates
that must not be place in the output area. However the code as is
produces duplicates. I am seking assitance to have this rectified. I
feel like I almost have it but just Missing something.
Any help is greatly appreciated.
Sub MixNumbers()
Dim holdNum(1 To 6) As Integer
Dim NumCount As Long
Dim r As Long
Dim T As Long
Dim n As Range
Dim S As Range
Dim d As Integer
Dim v As Variant
Dim x As Variant
Dim y As Range
Dim m As Long
Dim z As Integer
Dim rn As Long
Dim c As Long

rn = 1
c = 0

NumCount = 0
'Clear the target area
Range("Y2:Y5,S2:X2,K1:P65500").ClearContents
Application.StatusBar = ""

For T = 1 To 120
For r = 1 To 120
For Each n In Range(Cells(r, 1), Cells(r, 3))
For Each S In Range(Cells(T, 6), Cells(T, 8))
If S = n Then
c = c + 1
End If
Next S
Next n

If c = 0 Then
For Each v In Union(Range(Cells(T, 6), Cells(T,
8)), Range(Cells(r, 1), Cells(r, 3)))
d = d + 1
holdNum(d) = v
Next v
d = 0
'Temporarily place values in array on worksheet
Range(Cells(2, 17), Cells(2, 22)).Value =
holdNum()
'Check existing combination to see if there are
any matches
For m = 1 To NumCount
For Each y In Range(Cells(m, 11), Cells(m,
16))
For Each x In holdNum()
If y = x Then
z = z + 1
End If
' A exact match is found
If z = 4 Then
z = 0
Exit For
Else
End If
Next x
Next y
Next m
'Place value of array to target area on
worksheet
If z < 4 Then
Range(Cells(rn, 11), Cells(rn,
16)).Value = holdNum()
NumCount = NumCount + 1
Application.StatusBar = "Sets
Processed: " & Format(NumCount, ("#,##0#"))
rn = rn + 1
End If

End If ' IF C = 0
c = 0
Next r
Next T
'application.StatusBar = ""
End Sub

Problem with Looping and Output
 

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message ...
DNK,

It would help if you posted a small example of what your input and output
looks like, and how you want to determine 'duplicates'.

HTH,
Bernie
MS Excel MVP
The sample block below is used for testing of the code only. The
Block would be in a more randomized state. Duplicates are determined
by all the numbers in a set of block 1 match all the numbers in a set
in block 2. For testing purposes maybe less rows than what is
presented here can be used.


1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 4 5
1 4 6
1 4 7
1 4 8
1 4 9
1 4 10
1 5 6
1 5 7
1 5 8
1 5 9
1 5 10
1 6 7
1 6 8
1 6 9
1 6 10
1 7 8
1 7 9
1 7 10
1 8 9
1 8 10
1 9 10
2 3 4
2 3 5
2 3 6
2 3 7
2 3 8
2 3 9
2 3 10
2 4 5
2 4 6
2 4 7
2 4 8
2 4 9
2 4 10
2 5 6
2 5 7
2 5 8
2 5 9
2 5 10
2 6 7
2 6 8
2 6 9
2 6 10
2 7 8
2 7 9
2 7 10
2 8 9
2 8 10
2 9 10
3 4 5
3 4 6
3 4 7
3 4 8
3 4 9
3 4 10
3 5 6
3 5 7
3 5 8
3 5 9
3 5 10
3 6 7
3 6 8
3 6 9
3 6 10
3 7 8
3 7 9
3 7 10
3 8 9
3 8 10
3 9 10
4 5 6
4 5 7
4 5 8
4 5 9
4 5 10
4 6 7
4 6 8
4 6 9
4 6 10
4 7 8
4 7 9
4 7 10
4 8 9
4 8 10
4 9 10
5 6 7
5 6 8
5 6 9
5 6 10
5 7 8
5 7 9
5 7 10
5 8 9
5 8 10
5 9 10
6 7 8
6 7 9
6 7 10
6 8 9
6 8 10
6 9 10
7 8 9
7 8 10
7 9 10
8 9 10

Problem with Looping and Output
 

DNK,

Okay, that is block one. Does block two look similar, and what rule do you
use for the output? Do you want the similars, or only non-similars?

An example with even fewer rows but all three sets would be helpful: block
1, block 2, and the expected output.

Bernie

"Dnk" wrote in message
m...
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
DNK,

It would help if you posted a small example of what your input and
output
looks like, and how you want to determine 'duplicates'.

HTH,
Bernie
MS Excel MVP
The sample block below is used for testing of the code only. The
Block would be in a more randomized state. Duplicates are determined
by all the numbers in a set of block 1 match all the numbers in a set
in block 2. For testing purposes maybe less rows than what is
presented here can be used.


1 2 3
1 2 4
1 2 5
1 2 6
1 2 7
1 2 8
1 2 9
1 2 10
1 3 4
1 3 5
1 3 6
1 3 7
1 3 8
1 3 9
1 3 10
1 4 5
1 4 6
1 4 7
1 4 8
1 4 9
1 4 10
1 5 6
1 5 7
1 5 8
1 5 9
1 5 10
1 6 7
1 6 8
1 6 9
1 6 10
1 7 8
1 7 9
1 7 10
1 8 9
1 8 10
1 9 10
2 3 4
2 3 5
2 3 6
2 3 7
2 3 8
2 3 9
2 3 10
2 4 5
2 4 6
2 4 7
2 4 8
2 4 9
2 4 10
2 5 6
2 5 7
2 5 8
2 5 9
2 5 10
2 6 7
2 6 8
2 6 9
2 6 10
2 7 8
2 7 9
2 7 10
2 8 9
2 8 10
2 9 10
3 4 5
3 4 6
3 4 7
3 4 8
3 4 9
3 4 10
3 5 6
3 5 7
3 5 8
3 5 9
3 5 10
3 6 7
3 6 8
3 6 9
3 6 10
3 7 8
3 7 9
3 7 10
3 8 9
3 8 10
3 9 10
4 5 6
4 5 7
4 5 8
4 5 9
4 5 10
4 6 7
4 6 8
4 6 9
4 6 10
4 7 8
4 7 9
4 7 10
4 8 9
4 8 10
4 9 10
5 6 7
5 6 8
5 6 9
5 6 10
5 7 8
5 7 9
5 7 10
5 8 9
5 8 10
5 9 10
6 7 8
6 7 9
6 7 10
6 8 9
6 8 10
6 9 10
7 8 9
7 8 10
7 9 10
8 9 10