Count How Many Set Ranges Meet Condition in Above Current Row

ibmy

ibmy

Board Regular
Joined
Mar 4, 2020
Messages
134
Office Version
  1. 2016
Platform
  1. Windows
Hi,

1.11.xlsb
CDEFG
1Var AVar Bexpected result :
21.215271.21497
31.215321.2149940
41.215331.21499
51.215401.21499
61.215401.21491
71.215401.21498
81.215001.21492
91.215081.21503
101.215071.21498
111.215071.2150322
121.215091.21503
131.215111.21503
141.215141.21506
151.215151.2150910
161.215171.21509
171.215141.2150913
181.215171.21509
191.215171.2151461
201.215181.21514
211.215191.21514
221.215171.21514
231.215191.21514
241.215171.21514
251.215191.21514
261.215191.2151521
271.215211.21514
281.215191.21514
291.215171.21515411
301.215191.21514
311.215211.21514
321.215221.21514
331.215251.21514
341.215281.21524
351.215391.21531
361.215381.21530
371.215351.2153091
381.215381.21529
391.215381.21520
401.215381.21529
411.215381.21520
421.215381.21529
431.215381.21520
441.215381.21529
451.215371.21529
461.215371.21529
471.215201.2151410
481.215221.21514
491.215181.21511
501.215181.2151211
511.215201.21512
521.215221.21514
Sheet5

Explanation:
Let say Current Value Column D = B , Previous Value Column D = b ... Current Value Column C = A , Previous Value Column C = a
When there is a value in Column F ( F2<>"" / F2>0 ), count how many set ranges in Column C&D meet condition of both : a>=A AND b<=B . Stop count when one of it does not meet condition.
Example Row 3 : There is value in F3, so count how many set ranges meet condition a>=A AND b<=B , there is none, 0.
Example Row 29 : There is value in F29, there are 11 set ranges meet condition from row 28 to row 18 (Column C&D), stop at row 17 because 1.21514 (a) < 1.21517 (A)

Problem a bit similar to post Assign "1" but the difference are:
1. This post is about Count while previous post is about Assign.
2. This post is focus on previous cell/above row when there is value in Column F while previous post is assign from F2 to last row.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Sort by date Sort by votes
ibmy said:
Example Row 29 : There is value in F29, there are 11 set ranges meet condition from row 28 to row 18 (Column C&D), stop at row 17 because 1.21514 (a) < 1.21517 (A)
Click to expand...
Why is it 11? C26 is lower than C27, shouldn't it stops there?
 
Upvote 0
"C26 is higher than C29" AND "D26 is lower equal to D29" meet condition "a>=A" AND "b<=B"

"Current Value" = set as "Base Value".

If F29 <> "" / F29 >0 , then C29 , D29 set as "Base Value"/"Current Value" ( A , B )
Any cell above "Base Value" consider as "Previous Value" (a , b)

1) "C28 (a) 1.21519 >= 1.21517 (A) C29" AND "D28 (b) 1.21514 <= 1.21515 (B) D29"
2) "C27 (a) 1.21521 >= 1.21517 (A) C29" AND "D27 (b) 1.21514 <= 1.21515 (B) D29"
3) "C26 (a) 1.21519 >= 1.21517 (A) C29" AND "D26 (b) 1.21515 <= 1.21515 (B) D29"
4) "C25 (a) 1.21519 >= 1.21517 (A) C29" AND "D25 (b) 1.21514 <= 1.21515 (B) D29"
5) "C24 (a) 1.21517 >= 1.21517 (A) C29" AND "D24 (b) 1.21514 <= 1.21515 (B) D29"
6) "C23 (a) 1.21519 >= 1.21517 (A) C29" AND "D23 (b) 1.21514 <= 1.21515 (B) D29"
7) "C22 (a) 1.21517 >= 1.21517 (A) C29" AND "D22 (b) 1.21514 <= 1.21515 (B) D29"
8) "C21 (a) 1.21519 >= 1.21517 (A) C29" AND "D21 (b) 1.21514 <= 1.21515 (B) D29"
9) "C20 (a) 1.21518 >= 1.21517 (A) C29" AND "D20 (b) 1.21514 <= 1.21515 (B) D29"
10) "C19 (a) 1.21517 >= 1.21517 (A) C29" AND "D19 (b) 1.21514 <= 1.21515 (B) D29"
11) "C18 (a) 1.21517 >= 1.21517 (A) C29" AND "D18 (b) 1.21509 <= 1.21515 (B) D29"

STOP at Row 17 because C17 (a) 1.21514 < 1.21517 (A) C29
 
Upvote 0
Ok, try this:
VBA Code: 
Sub ibmy_1()
Dim i As Long, j As Long, k As Long
Dim A As Double, B As Double
Dim va, vc, vb
va = Range("C1", Cells(Rows.Count, "C").End(xlUp)).Resize(, 2)
vb = Range("F1", Cells(Rows.Count, "F").End(xlUp))
ReDim vc(1 To UBound(vb, 1), 1 To 1)
For i = 1 To UBound(vb, 1)
    If vb(i, 1) <> "" Then
        j = i
        A = va(i, 1)
        B = va(i, 2)
        For k = i - 1 To 2 Step -1
            If va(k, 1) >= A And va(k, 2) <= B Then
                'do nothing
            Else
                vc(i, 1) = j - k - 1
                Exit For
            End If
       Next
    End If
Next
'put result in H
Range("H1").Resize(UBound(vc, 1), 1) = vc
End Sub
 
Upvote 1
Solution
Thanks @Akuini , the code is working.

I try analyze "Single Column C, D", change abit code. For "single column C", the code is working but
for "single column D", there is problem, it start count at row 37, missing count at row 3. 11, 15, 17, 19, 26, 29.

A) Single Column C, working,
I remove:
VBA Code: 
And va(k, 2) <= B

VBA Code: 
Sub A_Up()
Dim i As Long, j As Long, k As Long
Dim A As Double, B As Double
Dim va, vc, vb
va = Range("C1", Cells(Rows.Count, "C").End(xlUp)).Resize(, 2)
vb = Range("F1", Cells(Rows.Count, "F").End(xlUp))
ReDim vc(1 To UBound(vb, 1), 1 To 1)
For i = 1 To UBound(vb, 1)
    If vb(i, 1) <> "" Then
        j = i
        A = va(i, 1)
        B = va(i, 2)
        For k = i - 1 To 2 Step -1
            If va(k, 1) >= A Then
                'do nothing
            Else
                vc(i, 1) = j - k - 1
                Exit For
            End If
       Next
    End If
Next
'put result in H
Range("I1").Resize(UBound(vc, 1), 1) = vc
End Sub

B) Single Column D, missing count at row 3. 11, 15, 17, 19, 26, 29.
I remove :
VBA Code: 
va(k, 1) >= A And

VBA Code: 
Sub B_Up()
Dim i As Long, j As Long, k As Long
Dim A As Double, B As Double
Dim va, vc, vb
va = Range("C1", Cells(Rows.Count, "C").End(xlUp)).Resize(, 2)
vb = Range("F1", Cells(Rows.Count, "F").End(xlUp))
ReDim vc(1 To UBound(vb, 1), 1 To 1)
For i = 1 To UBound(vb, 1)
    If vb(i, 1) <> "" Then
        j = i
        A = va(i, 1)
        B = va(i, 2)
        For k = i - 1 To 2 Step -1
            If va(k, 2) <= B Then
                'do nothing
            Else
                vc(i, 1) = j - k - 1
                Exit For
            End If
       Next
    End If
Next
'put result in H
Range("J1").Resize(UBound(vc, 1), 1) = vc
End Sub
 
Upvote 0
Ok, on the original code I added this line: vc(i, 1) = 0 '<-- new line added
change Sub A_Up & Sub B_Up accordingly, see if it resolves the issue
VBA Code: 
Sub ibmy_2()
Dim i As Long, j As Long, k As Long
Dim A As Double, B As Double
Dim va, vc, vb
va = Range("C1", Cells(Rows.Count, "C").End(xlUp)).Resize(, 2)
vb = Range("F1", Cells(Rows.Count, "F").End(xlUp))
ReDim vc(1 To UBound(vb, 1), 1 To 1)
For i = 1 To UBound(vb, 1)
    If vb(i, 1) <> "" Then
        vc(i, 1) = 0   '<-- new line added
        j = i
        A = va(i, 1)
        B = va(i, 2)
        For k = i - 1 To 2 Step -1
            If va(k, 1) >= A And va(k, 2) <= B Then
                'do nothing
            Else
                vc(i, 1) = j - k - 1
                Exit For
            End If
       Next
    End If
Next
'put result in H
Range("H1").Resize(UBound(vc, 1), 1) = vc
End Sub
 
Upvote 1
Thanks again @Akuini , the code working great.

Can I request one last code for Single Column C,D Down (A_Down & B_Down).
Same as A_Up & B_Up but this one start count below "Base Value".

2.22.xlsb
CDEFGHIJKL
1Var AVar BA DownB down
21.215271.21497
31.215321.21499434
41.215331.21499
51.21541.21499
61.21541.21491
71.21541.21498
81.2151.21492
91.215081.21503
101.215071.21498
111.215071.215032402
121.215091.21503
131.215111.21503
141.215141.21506
151.215151.21509113
161.215171.21509
171.215141.215091341
181.215171.21509
191.215171.215146326
201.215181.21514
211.215191.21514
221.215171.21514
231.215191.21514
241.215171.21514
251.215191.21514
261.215191.21515227
271.215211.21514
281.215191.21514
291.215171.215154224
301.215191.21514
311.215211.21514
321.215221.21514
331.215251.21514
341.215281.21524
351.215391.21531
361.215381.2153
371.215351.21539915
381.215381.21529
391.215381.2152
401.215381.21529
411.215381.2152
421.215381.21529
431.215381.2152
441.215381.21529
451.215371.21529
461.215371.21529
471.21521.21514115
481.215221.21514
491.215181.21511
501.215181.21512111
511.21521.21512
521.215061.21514
Sheet4
 
Upvote 0
ibmy said:
Can I request one last code for Single Column C,D Down (A_Down & B_Down).
Same as A_Up & B_Up but this one start count below "Base Value".
Click to expand...
Try this;
VBA Code: 
Sub ibmy_3A()
Dim i As Long, j As Long, k As Long
Dim A As Double, B As Double
Dim va, vc, vb
va = Range("C1", Cells(Rows.Count, "C").End(xlUp)).Resize(, 2)
vb = Range("F1", Cells(Rows.Count, "F").End(xlUp))
ReDim vc(1 To UBound(vb, 1), 1 To 1)
For i = 1 To UBound(vb, 1)
    If vb(i, 1) <> "" Then
        vc(i, 1) = 0   '<-- new line added
        j = i
        A = va(i, 1)
        B = va(i, 2)
        For k = i + 1 To UBound(va, 1)
            If va(k, 1) >= A Then
                'do nothing
            Else
                vc(i, 1) = k - j - 1
                Exit For
            End If
       Next
    End If
Next
'put result in H
Range("H1").Resize(UBound(vc, 1), 1) = vc
End Sub


VBA Code: 
Sub ibmy_3B()
Dim i As Long, j As Long, k As Long
Dim A As Double, B As Double
Dim va, vc, vb
va = Range("C1", Cells(Rows.Count, "C").End(xlUp)).Resize(, 2)
vb = Range("F1", Cells(Rows.Count, "F").End(xlUp))
ReDim vc(1 To UBound(vb, 1), 1 To 1)
For i = 1 To UBound(vb, 1)
    If vb(i, 1) <> "" Then
        vc(i, 1) = 0   '<-- new line added
        j = i
        A = va(i, 1)
        B = va(i, 2)
        For k = i + 1 To UBound(va, 1)
            If va(k, 2) <= B Then
                'do nothing
            Else
                vc(i, 1) = k - j - 1
                Exit For
            End If
       Next
    End If
Next
'put result in I
Range("I1").Resize(UBound(vc, 1), 1) = vc
End Sub
 
Upvote 1
Thanks @Akuini ,the code is working as intended.
I appreciate your help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
conditional formatting (or VBA?) to go to a specific row number based on the current date
Replies
4
Views
207
kbishop94
K
M
  • Solved
Condition formatting - count how many of the same color OR value appears in the list ?
Replies
5
Views
131
Asbestos_Jen
Asbestos_Jen
R
  • Solved
Excel/Google Sheets COUNTIFS formula to count rows, but with a substring in the Range.
2
Replies
11
Views
589
Fluff
Fluff
D
  • Solved
Counting Consecutive Blanks
Replies
9
Views
643
felixstraube
F
F
  • Solved
SUMIF and AVERAGEIF two separate columns, only picking the greater value between the two column.
Replies
5
Views
288
hagia_sofia
hagia_sofia

Forum statistics

Threads
1,223,867
Messages
6,175,062
Members
452,610
Latest member
Sherijoe

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