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

M

Mark

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
"[email protected]", removing the .DELETE from
the end of my address.


Thanks, Mark Pyne
 
B

BrianB

This is not a trivial undertaking because it is necessary to check eac
set of numbers in a line with every other set in each of the othe
lines. also because there is no idea of how many numbers make up
"set".

Here is a snippet of similar code from a larger routine that looks i
a list for a set of numbers that make up a particular total. Thi
subroutine looks for 3 numbers. The overall routine looks for sets o
2,3, ... up to 7 numbers. This is much simpler than your requirement.

Although the list of numbers never contains much more than about 10
items, once we get to 4 numbers that section of code begins to tak
over 6 hours to run. After an overnight run the operator prefers t
stop the process.

'-------------------------------------------
Sub numbers3()
CurrentSet = "3 Numbers"
record_time
For n1 = 1 To LastRow
For n2 = 2 To LastRow
For n3 = 3 To LastRow

'------------------------------------------------------------------
Application.StatusBar = " 3 Numbers " & n1 & ":" & n2
":" & n3
X = Numset(n1) + Numset(n2) + Numset(n3)
If rsp = vbCancel Then End
If Abs(CheckNum - X) < 1 Then
record_result ' sub routine to stop theprocess
End If

'-------------------------------------------------------------------
Next n3
Next n2
Next n1
End Sub
'------------------------------------------
 
Top