Address of the First Five Consecutive rows where the cell value is >=X

[Anilkn]

Hi,

I’m trying to find the address of the First Five Row numbers in a given column where continuously the cell values are >= X. There can be multiple instance where we can find continuous rows where cell values are >=X, but I need the first occurrence only.

For example in column A, continuously values in the 5 rows i.e., A5 A6 A7 A8 A9 are >=x(10) and also in row number A13 A14 A15 A16 A17 etc. But the row# A5 to A9 is the first occurrence (first five consecutive values) and I need the address of the row numbers as output i.e., A5:A9


Row# Column A

1	2
2	12
3	14
4	8
5	10
6	10
7	10
8	10
9	10
10	2
11	2
12	5
13	10
14	10
15	10
16	10
17	10
18	10
19	5
20	3

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[MickG]

Try this UDF:-

Function Seq(Rng [COLOR="Navy"]As[/COLOR] Range) [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]Dim[/COLOR] K [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] K [COLOR="Navy"]In[/COLOR] .Keys
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] .Item(K).Areas
    [COLOR="Navy"]If[/COLOR] Dn.Count >= 5 [COLOR="Navy"]Then[/COLOR]
        Seq = Dn.Address
        [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]Next[/COLOR] K
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]End[/COLOR] Function

To Save and Run UDF:-
Copy Function from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

In sheet, Add the "function" in a cell as :- =Seq(A2:A23)
NB:- Once you've added the first bracket you can select the range from the sheet
Then closing Bracket, then click "Enter"
The cell should now show the result, in this case $A$5:$A$9
.


Regards Mick

 

[Anilkn]

Dear Mick,

Thank you for the code and it does extract the first five consecutive values. But i need these first five consecutive values to be greater than or equal to value X, where X is a failure criterion value, and I have to give this value as an input during the calculations (In general, the value X is stored in the first row of the column).

Could you please incorporate this additional step in your below code?

With Regards,
Anilkumar KN

Try this UDF:-

Function Seq(Rng [COLOR=Navy]As[/COLOR] Range) [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Dim[/COLOR] Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR=Navy]Else[/COLOR]
        [COLOR=Navy]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]Dim[/COLOR] K [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .Keys
 [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] .Item(K).Areas
    [COLOR=Navy]If[/COLOR] Dn.Count >= 5 [COLOR=Navy]Then[/COLOR]
        Seq = Dn.Address
        [COLOR=Navy]Exit[/COLOR] For
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]End[/COLOR] With

[COLOR=Navy]End[/COLOR] Function

To Save and Run UDF:-
Copy Function from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.

In sheet, Add the "function" in a cell as :- =Seq(A2:A23)
NB:- Once you've added the first bracket you can select the range from the sheet
Then closing Bracket, then click "Enter"
The cell should now show the result, in this case $A$5:$A$9
.


Regards Mick

 

[Yongle]

Try this

Public Function Get_Address(rng As Range, x As Double) As String
    Dim arr, a As Long
    arr = rng
    
    For a = 1 To UBound(arr) - 4
        If arr(a, 1) >= x And arr(a + 1, 1) >= x And arr(a + 2, 1) >= x And arr(a + 3, 1) >= x And arr(a + 4, 1) >= x Then
            Get_Address = rng.Cells(a).Resize(5).Address(0, 0)
            Exit Function
        End If
    Next a
    If Get_Address = "" Then Get_Address = "not found"
End Function

Use in VBA like this

Sub CallFunction()
    MsgBox Get_Address(Range("A:A"), 10)
    MsgBox Get_Address(Range("A:A"), Range("F1"))
End Sub

Use as UDF in Excel like this

Excel 2016 (Windows) 32 bit

	A​	B​	C​	D​	E​	F​
1​	Value		Result		X =	10​
2​	1​			Formula
3​	2​		A12:A16	=Get_Address(A:A,10)
4​	3​		A12:A16	=Get_Address(A2:A33,10)
5​	4​
6​	5​
7​	6​
8​	7​		A12:A16	=Get_Address(A:A,F1)
9​	8​		A12:A16	=Get_Address(A2:A33,F1)
10​	9​
11​	5​
12​	10​
13​	11​
14​	11​
15​	10​
16​	27​
17​	4​
18​	5​
19​	6​
20​	7​
21​	8​
22​	9​
23​	12​
24​	13​
25​	14​
26​	15​
27​	16​
28​	17​
29​	1​
30​	2​
31​	3​
32​	4​
33​	5​
34​

Sheet: Sheet9

 

[Anilkn]

Dear Yongle,

This is exactly what i was looking for, and it is going to save enormous time in analysing huge amount of data.

Thank you very much

With Regards,
Anilkumar K N

 

[MickG]

Deleted by MickG