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,

--
dkenebr

-----------------------------------------------------------------------
dkenebre's Profile: http://www.excelforum.com/member.php...info&userid=76
View this thread: http://www.excelforum.com/showthread.php?threadid=39479

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" wrote:


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

Also, in the sample data below, the #6 occurred in row 63 and then in
row 65, so the output is 1 in GO65. #17 occurred in row 63 and then in
row 65, so the output is 1 in GP65, #19 has not occurred so it receive
an "X" in GQ65. #27 occurred in row 64, then in row 65, the output is 0
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, X

GE65:GH65 = 6, 17, 19, 27 €¦€¦€¦€¦€¦GO65:GR65 = 1, 1, X, 0


--
dkenebre


------------------------------------------------------------------------
dkenebre's Profile: http://www.excelforum.com/member.php...nfo&userid=760
View this thread: http://www.excelforum.com/showthread...hreadid=394790

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


--
dkenebre


------------------------------------------------------------------------
dkenebre's Profile: http://www.excelforum.com/member.php...nfo&userid=760
View this thread: http://www.excelforum.com/showthread...hreadid=394790