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

A

Anilkn

New Member
Joined
Jul 9, 2019
Messages
3
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
[TABLE="width: 117"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]14[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]15[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]16[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]17[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]18[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD="align: right"]19[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]20[/TD]
[TD="align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
Sort by date Sort by votes
Try this UDF:-
Code: 
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
 
Last edited:
Upvote 0
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


MickG said:
Try this UDF:-
Code: 
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
Click to expand...
 
Upvote 0
Try this

Code: 
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
Code: 
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
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]
A​
[/th][th]
B​
[/th][th]
C​
[/th][th]
D​
[/th][th]
E​
[/th][th]
F​
[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
1​
[/td][td]Value[/td][td][/td][td]Result[/td][td][/td][td]X = [/td][td]
10​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
2​
[/td][td]
1​
[/td][td][/td][td][/td][td]Formula[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
3​
[/td][td]
2​
[/td][td][/td][td]A12:A16[/td][td] =Get_Address(A:A,10)[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
4​
[/td][td]
3​
[/td][td][/td][td]A12:A16[/td][td] =Get_Address(A2:A33,10)[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
5​
[/td][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
6​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
7​
[/td][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
8​
[/td][td]
7​
[/td][td][/td][td]A12:A16[/td][td] =Get_Address(A:A,F1)[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
9​
[/td][td]
8​
[/td][td][/td][td]A12:A16[/td][td] =Get_Address(A2:A33,F1)[/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
10​
[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
11​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
12​
[/td][td=bgcolor:#E2EFDA]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
13​
[/td][td=bgcolor:#E2EFDA]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
14​
[/td][td=bgcolor:#E2EFDA]
11​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
15​
[/td][td=bgcolor:#E2EFDA]
10​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
16​
[/td][td=bgcolor:#E2EFDA]
27​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
17​
[/td][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
18​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
19​
[/td][td]
6​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
20​
[/td][td]
7​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
21​
[/td][td]
8​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
22​
[/td][td]
9​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
23​
[/td][td]
12​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
24​
[/td][td]
13​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
25​
[/td][td]
14​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
26​
[/td][td]
15​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
27​
[/td][td]
16​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
28​
[/td][td]
17​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
29​
[/td][td]
1​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
30​
[/td][td]
2​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
31​
[/td][td]
3​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
32​
[/td][td]
4​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
33​
[/td][td]
5​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]
34​
[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet9[/td][/tr][/table]
 
Last edited:
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Question Question
How to count the five consecutive 'x' value within the 10 horizontal cells
2
Replies
11
Views
425
bebo021999
B
I
  • Solved
Formula to count consecutive zeros at the end of a row
Replies
7
Views
308
Peter_SSs
Peter_SSs
R
  • Solved
TEXTSPLIT into Rows but Maintain Grouping
Replies
5
Views
112
Cubist
Cubist
G
  • Solved
Formula or macro to retrieve cell above to replace zero
Replies
3
Views
96
Peter_SSs
Peter_SSs
N
  • Question Question
AutoFilter Field Macro Returns a Header Row only
2
Replies
11
Views
221
MARK858
MARK858

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top