Find repetition in data values

[theta]

Hi all,

I have a column of values and I would like to find repeated patterns using VBA or a formula (preferable for quick tweaking). I am going to group items using the [+] and [-] collapsing frames. I could do this using an exhaustive IF formula going to about 5 levels checking (row1 vs row2), (row1 & row2 vs row3 & row4), (row1 & row2 & row3 vs row4 & row5 & row6) but this is a horrible methods.

Any help appreciated, sample data below :

Hello (recursive 2 levels)
Goodbye (recursive 2 levels)
Hello
Goobye
Hello
Goodbye
Red (recursive 3 levels)
Blue (recursive 3 levels)
Green (recursive 3 levels)
Red
Blue
Green
Apple (recursive 4 levels)
Orange (recursive 4 levels)
Pear (recursive 4 levels)
Banana (recursive 4 levels)
Apple
Orange
Pear
Banana

[mdmackillop]

I don't see where you are going with this, but this code reflects the recursive levels stated in your sample data
[VBA]Option Explicit
Sub Groups()
Dim r As Range, cel As Range, i As Long
Set r = Range("A1:A20")
For i = 8 To 2 Step -1
For Each cel In r
If cel = cel.Offset(i) Then cel.Offset(, 1) = i
Next
Next
End Sub[/VBA]

[theta]

Thanks MD! Given me alot to work with there, like the approach.

I am working with a call tree listing macro but it produces all recursive events. So I will append this as part of the tidy up / format report code before presenting the result to the user.

Saved me alot of headache