Keep assign "1" unless A more than "x" or B les than "y" or Both

[ibmy]

Hi,

Explanation in visual :

Explanation in text:
1) Keep assign "1" in column E if "A" value less than or same as starting "A" value AND "B" value more than or same as starting "B" value.
2) Stop assign "1" in column E if "A" value more than starting "A" value OR "B" value less than starting "B" value OR both happened.

less more.xlsx
	C	D	E	F	G
1	"A"	"B"	Result		explanation:
2	1.19048	1.19044			Start here, "A" number : 1.19048 , "B" number : 1.19044
3	1.19045	1.19044	1
4	1.19046	1.19044	1
5	1.19046	1.19045	1
6	1.19047	1.19046	1
7	1.19048	1.19043			"B" number less than 1.19044. New "A" number : 1.19048 , "B" number : 1.19043
8	1.19049	1.19045			"A" number more than 1.19048. New "A" number : 1.19049, "B" number : 1.19045
9	1.19048	1.19046	1
10	1.19048	1.19047	1
11	1.19050	1.19042			"A" number more than 1.19049 and "B" number less than 1.19045. New "A" number : 1.19050, "B" number : 1.19042
12	1.19049	1.19045	1
13	1.19048	1.19047	1
14	1.19046	1.19044	1
15	1.19047	1.19045	1
16	1.19047	1.19046	1
17	1.19053	1.19044			"A" number more than 1.19050. New "A" number : 1.19053, "B" number : 1.19044
18	1.19047	1.19044	1
19	1.19050	1.19045	1
20	1.19044	1.19039			"B" number less than 1.19044. New "A" number : 1.19044 , "B" number : 1.19039
21	1.19045	1.19028			"A" number more than 1.19044 and "B" number less than 1.19039. New "A" number : 1.19045, "B" number : 1.19028
22	1.19060	1.19027			"A" number more than 1.19045. New "A" number : 1.19060, "B" number : 1.19027
Sheet5

Prefer VBA code solution but if formula solution can get 250k row data get result faster, dont matter.

 

[JamesCanale]

MrExcelPlayground22.xlsx
	C	D	E	F	G
1	"A"	"B"	Result	Result	explanation:
2	1.19048	1.19044			Start here, "A" number : 1.19048 , "B" number : 1.19044
3	1.19045	1.19044	1	1
4	1.19046	1.19044	1	1
5	1.19046	1.19045	1	1
6	1.19047	1.19046	1	1
7	1.19048	1.19043			"B" number less than 1.19044. New "A" number : 1.19048 , "B" number : 1.19043
8	1.19049	1.19045			"A" number more than 1.19048. New "A" number : 1.19049, "B" number : 1.19045
9	1.19048	1.19046	1	1
10	1.19048	1.19047	1	1
11	1.1905	1.19042			"A" number more than 1.19049 and "B" number less than 1.19045. New "A" number : 1.19050, "B" number : 1.19042
12	1.19049	1.19045	1	1
13	1.19048	1.19047	1	1
14	1.19046	1.19044	1	1
15	1.19047	1.19045	1	1
16	1.19047	1.19046	1	1
17	1.19053	1.19044			"A" number more than 1.19050. New "A" number : 1.19053, "B" number : 1.19044
18	1.19047	1.19044	1	1
19	1.1905	1.19045	1	1
20	1.19044	1.19039			"B" number less than 1.19044. New "A" number : 1.19044 , "B" number : 1.19039
21	1.19045	1.19028			"A" number more than 1.19044 and "B" number less than 1.19039. New "A" number : 1.19045, "B" number : 1.19028
22	1.1906	1.19027			"A" number more than 1.19045. New "A" number : 1.19060, "B" number : 1.19027
Sheet12

Cell Formulas
Range	Formula
F3:F22	F3	=IF(OR(C3>MAX(C$2:C2),D3<MIN(D$2:D2)),"",1)

 

[ibmy]

@JamesCanale Thanks for reply and the formula.

There some row assigned "1" wrongly.

My bad, I forgot to highlight that if next row "A" value more than base "A" value OR next row "B" value less than base "B" value OR both happened then there are new base "A" value and base "B" value (row 7, 8 , 11, 17, 20 , 21 ,22 )

 

[Alex Blakenburg]

If after trying James's suggestion you find that you still need VBA, then give this a try:

Sub FlagRows()

    Dim ws As Worksheet
    Dim rng As Range
    Dim arr As Variant
    Dim compareToA As Variant, compareToB
    Dim valA As Variant, valB As Variant
    Dim rowLast As Long, i As Long
    
    Set ws = ActiveSheet            ' Ideally name the sheet using Worksheets("YourSheetName")
    With ws
        rowLast = .Range("C" & Rows.Count).End(xlUp).Row
        Set rng = .Range("C2", .Cells(rowLast, "E"))
        rng.Columns(3).ClearContents
        arr = rng.Value2
    End With
    
    compareToA = CDec(Application.Round(arr(1, 1), 8))
    compareToB = CDec(Application.Round(arr(1, 2), 8))
    
    For i = 2 To UBound(arr)
        valA = CDec(Application.Round(arr(i, 1), 8))
        valB = CDec(Application.Round(arr(i, 2), 8))
        If valA <= compareToA And valB >= compareToB Then
            arr(i, 3) = 1
        Else
            compareToA = CDec(Application.Round(arr(i, 1), 8))
            compareToB = CDec(Application.Round(arr(i, 2), 8))
        End If
    Next i

    rng.Columns(3).Value = Application.Index(arr, 0, 3)
End Sub

 

[ibmy]

@Alex Blakenburg Thanks for reply and the code. The code works perfectly.

Thank you very much for spend time writing the code, I appreciate it.
I wish you have a great day.