Excel finding patterns across data range

Hi

I have a sheet with 6 rows of data per entry and a column indicating if the data is positive or negative, ((1 for positive) can excel analyse the numbers across the rows to find a pattern if indicated as positive?


Thanks for any help offered

And more specifically .. how do you determine a row is either positive or negative ? There doesn't seem to be a correlation with the numbers ???

I am trying to see what numbers from what columns form a pattern when i deem it a positive result by entering 1 into a row in column h

For example if the result is positive does the number 3 appear often in column b or the number 2 in column c etc

Paste this into a Routine Module, activate from a Command Button on Sheet 1:

Option Explicit

Sub test10()

Dim rCell As Range
Dim rRng As Range
Dim r1 As Range
Dim r2 As Range
Dim i, y As Integer
Dim xcell
Dim ycell
Set rRng = Sheets("Sheet1").Range("A1:F151") '-1 on column due to offset

i = 1 'row length
y = 0 'column length

'Scan through all cells in range and find pattern

For Each rCell In rRng.Cells
If rCell.Value = "" Then GoTo skip
i = 1
    Do Until i = 6
    y = 0
        Do Until y = 6
         xcell = rCell.Value & rCell.Offset(0, 1).Value
         Set r1 = Range(rCell, rCell.Offset(0, 1))
         r1.Select

         ycell = rCell.Offset(i, y).Value & rCell.Offset(i, y + 1).Value
         Set r2 = Range(rCell.Offset(i, y), rCell.Offset(i, y + 1))

            If ycell = xcell Then

                    Union(r1, r2).Font.Bold = True
                    Union(r1, r2).Font.Italic = True
                    Union(r1, r2).Font.Color = &HFF&
                    MsgBox "Match Found at: " & rCell.Address & ":" & rCell.Offset(0, 1).Address & " and " & rCell.Offset(i, y).Address & ":" & rCell.Offset(i, y + 1).Address
                    Union(r1, r2).Font.Bold = False
                    Union(r1, r2).Font.Italic = False
                    Union(r1, r2).Font.Color = &H0&
            End If
            y = y + 1
            Loop
            i = i + 1
        Loop
skip:
Next rCell


End Sub

Move your Positve/Negative column to Col M. This will prevent the code from considering the 1's and 0's in that column as part of the pattern.

Thanks for your help i will give it a go.

H