Function/VB Code for finding pattern blocks in number sets?
 

I have several sets of numbers, e.g. :

set 1 : 5, 4, 12, 15, 19, 15, 16, 19, 18, 21, 3, ....
set 2 : 1, 7, 4, 9, 22, 83, 17, 61, 1, 73, 15, 16, 19, ...
set 3 : 7, 93, 14, 73, 14, 25, 82, 21, 12, 17, ...
and so on...

and I am looking for a way to get excel to read through the numbers
and find "blocks" of duplicated patterns between the different sets.
For example, if I ran it through the above sets, it would point out
"15, 16, 19" in set 1 and set 2 either by (prefably) highlighting the
matching numbers, or just outputting them to a column/file. The sets
of numbers contain a different amount of numbers, and do not
necessarily have a matching block in another set.

I presume this is possible with a small piece of visual basic code
within excel, though am unfamiliar with the functions within excel and
basic? Any advice/pointers in the right direction would be
appreciated.

Please prefably reply in the newsgroup, or via email to
", removing the .DELETE from
the end of my address.


Thanks, Mark Pyne

Function/VB Code for finding pattern blocks in number sets?
 

This looked like a looping problem...


Sub test()
Dim set1 As Variant, set2 As Variant, set3 As Variant
Dim SetS As Variant, SetD As Variant, SetT As Variant
Dim i As Long, j As Long, k As Long, l As Long, bln As Boolean

set1 = Array(5, 4, 12, 15, 19, 15, 16, 19, 18, 21, 3)
set2 = Array(1, 7, 4, 9, 22, 83, 17, 61, 1, 73, 15, 16, 19)
set3 = Array(7, 93, 14, 73, 14, 25, 82, 21, 12, 17)

SetS = set1
SetD = set2

If UBound(SetD) < UBound(SetS) Then
SetT = SetS
SetS = SetD
SetD = SetT
End If

For k = UBound(SetS) To 0 Step -1
For i = 0 To UBound(SetS) - (k - 1)
For j = 0 To UBound(SetD) - (k - 1)
bln = True
For l = 0 To k - 1
If SetS(i + l) < SetD(j + l) Then bln = False
Next
If bln Then Exit For
Next
If bln Then Exit For
Next
If bln Then Exit For
Next
If bln Then
For l = 0 To k - 1
Debug.Print SetS(i + l)
Next
End If

End Sub


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Mark" wrote in message
...
I have several sets of numbers, e.g. :

set 1 : 5, 4, 12, 15, 19, 15, 16, 19, 18, 21, 3, ....
set 2 : 1, 7, 4, 9, 22, 83, 17, 61, 1, 73, 15, 16, 19, ...
set 3 : 7, 93, 14, 73, 14, 25, 82, 21, 12, 17, ...
and so on...

and I am looking for a way to get excel to read through the numbers
and find "blocks" of duplicated patterns between the different sets.
For example, if I ran it through the above sets, it would point out
"15, 16, 19" in set 1 and set 2 either by (prefably) highlighting the
matching numbers, or just outputting them to a column/file. The sets
of numbers contain a different amount of numbers, and do not
necessarily have a matching block in another set.

I presume this is possible with a small piece of visual basic code
within excel, though am unfamiliar with the functions within excel and
basic? Any advice/pointers in the right direction would be
appreciated.

Please prefably reply in the newsgroup, or via email to
", removing the .DELETE from
the end of my address.


Thanks, Mark Pyne