Hi Howard,

Am Tue, 9 Dec 2014 00:13:28 -0800 (PST) schrieb L. Howard:

The values in column A are temperatures listed at a given interval. The user enters the value in F1. If he wants to know how many times the temp was the same for 11 "intervals" (days hours or what ever the interval is) then F1 is set to 11.

The code finds any entry in column A that occurs consecutively the number of time as F1's value and posts it in column B at the cell where the 11th occurrence is. So in column B there are a number of scattered entries that look like "10.22 = 11 times" or "9.15 = 11 times" etc.

Then all those values in column B are consolidated in column C for easy reading, instead of having to scroll down many rows/pages.

try:

Sub AnyDupesNumF1()

Dim i As Long, lr As Long, j As Long, k As Long

Dim vArray As Variant, varOut() As Variant

[B:C].ClearContents

With Sheets("Sheet1")

lr = .Cells(Rows.Count, "A").End(xlUp).Row

vArray = .Range("A1:A" & lr)

k = .Range("F1")

For i = 2 To UBound(vArray)

If vArray(i, 1) = vArray(i - 1, 1) Then

j = j + 1

If j = k Then

.Cells(i - 1, 2) = vArray(i, 1) & " = " & j

j = 0

End If

Else

j = 0

End If

Next 'i

lr = .Cells(Rows.Count, 2).End(xlUp).Row

vArray = .Range("B1:B" & lr)

k = 0

ReDim Preserve varOut(WorksheetFunction.CountA(.Range("B:B")) - 1, 0)

For i = LBound(vArray) To UBound(vArray)

If Len(vArray(i, 1)) > 0 Then

varOut(k, 0) = vArray(i, 1)

k = k + 1

End If

Next

.Range("C1").Resize(k) = varOut

End With

End Sub

or try in C1 a formula:

=IFERROR(INDIRECT("B"&SMALL(IF(B1:B2000<>"",ROW(1:2000)),ROW(A1))),"")

and insert the formula with CTRL+Shift+Enter

Regards

Claus B.