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.

 

[ibmy]

@Alex Blakenburg , Been using your code almost 1 month in experimenting data, so far the code is great. Thanks.
I have 4 questions regarding the code..

1)

Dim compareToA As Variant, compareToB

Does the code missing declare compareToB as Variant ?

2)

compareToA = CDec(Application.Round(arr(1, 1), 8))

What value "8" refer to?

3)

If valA < compareToB And valB < compareToB Or valB > compareToA And valA > compareToA Then

Is this the right way to write 2 condition for OR function in this code? :

Condition 1 : valA less than compareToB AND valB less than compareToB
Or
Condition 2 : valB more than compareToA AND valA more than compareToA
So far the result is right but need your confirmation if I missing any other line to edit/change?

4)

rowLast = .Range("D" & Rows.Count).End(xlUp).Row

Set rng = .Range("D3", .Cells(rowLast, "H"))

rng.Columns(5).ClearContents

compareToB = CDec(Application.Round(arr(1, 5), 8))

valB = CDec(Application.Round(arr(i, 5), 8))

arr(i, 5) = 1

rng.Columns(5).Value = Application.Index(arr, 0, 5)

If I change data column placement of C,D,E to D,F,H , does these 7 lines need to change or there are any other lines need to edit/change?
So far the result is ok but need your confirmation.

 

[Alex Blakenburg]

1) compareToB
Yes omitting the type in the Dim statement means it defaults to variant. I would have peferred to actually have declared it "as Variant" so it works but was an oversight on my part.

2) compareToA = CDec(Application.Round(arr(1, 1), 8))
I was trying to head avoid having a floating point error by applying rounding that would not impact that would not comprise the number of significant digits required and decided on 8 decimal places. The Decimal data type does not use the floating point method so should not introduce further floating point errors.

3) And and Or in If statement.
I believe it would work but for clarity I would put brackets around each of the And pairings
If (valA < compareToB And valB < compareToB) Or (valB > compareToA And valA > compareToA) Then

4) Only the "compareToB =" and "ValB =" lines look to be wrong.
Original range C,D,E and loaded into arr as 3 columns
New range D,E,F,G,H (although you are only interested in the 3 colums D,F,H) all 5 columns are loaded to arr.
D is column 1 in "arr", F is column 3, H is column 5.
compareToB and ValB now refer to column F so should both refer to arr(i,3)