Code occurrences between rows

[dkenebre]

How do I code to
Record the number of rows between a number and it’s previous occurrenc
within the source data range GE63:GM116. The output range is GO63:GW116
If there is no previous occurrence within the source data range the
record and X. If it occurred in the previous row then return a 0 as th
result. If it’s two previous rows, then return a 1 and so on. Return al
empty cells within the source range with an empty output.

Also, in the sample data below, the #6 occurred in row 63 and then i
row 65, so the output is 1 in GO65. #17 occurred in row 63 and then i
row 65, so the output is 1 in GP65, #19 has not occurred so it receiv
an "X" in GQ65. #27 occurred in row 64, then in row 65, the output is
in GR65. GS65:GW65 are empty.

example:
Sample data………………………………………….…..Result
GE63:GJ63 = 1, 5, 6, 17, 21, 26……………GO63:GU63 = X, X, X, X, X, X
GE64:GK64 = 1, 5, 9, 16, 21, 26, 27……………GO64:GT64 = 0, 0, X, X, 0, 0,

GE65:GH65 = 6, 17, 19, 27 ……………GO65:GR65 = 1, 1, X,

 

[Toppers]

Sub Count_occurences()

Dim c(100) As Integer ' <==== change if number value can be greater than 100

For i = 1 To 100 ' <==== change if number value can be greater than 100
c(i) = 0
Next i

For Row = 63 To 116
col = 187 ' GE
Do While Cells(Row, col) <> ""
n = Cells(Row, col).Value
If c(n) = 0 Then
Cells(Row, col).Offset(0, 10) = "X"
Else
Cells(Row, col).Offset(0, 10) = Row - c(n) - 1
End If
c(n) = Row ' Store last row number
col = col + 1
Loop
Next Row
End Sub


Acknowledgement would be appreciated!

 

[dkenebre]

Thanks that worked fine. I just a clearcontents before execution. I
appreciate the help