Comparing last step to current row

[Pestomania]

I am trying to identify a way to develop a formula which allows me to "scan" all of the work tickets and identify if the row I am on is before or after the last step scanned.

I.e. Step 10 is less than Step 30 which was scanned on 1/3/2023. Anything before Step 30 should be reviewed.

Book1
	A	B	C	D	E	F	G
1	Work Ticket Number	Step Number	Date Scanned	Last Step Scanned	Should I review?
2	1	10	1/1/2023	1 - 30	Yes		The rule is: If a step 30 has been scanned but step 20 has not, step 20 needs to be reviewed for accuracy.
3	1	20		1 - 30	Yes
4	1	30	1/3/2023	1 - 30	Yes		What I need help determining is "what is the last step scanned on the unique work ticket and is that row prior to that last step"?
5	1	40		1 - 30	No
6	12	10	3/1/2023	12 - 50	Yes
7	12	20		12 - 50	Yes
8	12	30		12 - 50	Yes
9	12	40		12 - 50	Yes
10	12	50	3/3/2023	12 - 50	Yes
11	12	60		12 - 50	No
Sheet1

 

[Pestomania]

Ok, how about

Fluff.xlsm
	A	B	C	D	E	F
1	WorkTicketNo	StepNo	DateUpdated	StatusCode	Max Step Scanned	Is this Step less than 170?
2	22243	10	45013	NEW		yes
3	22243	31	45013	COM		yes
4	22243	31	45013	COM		yes
5	22243	70	45013	COM		yes
6	22243	91	45013	COM		yes
7	22243	91	45013	COM		yes
8	22243	100	45013	COM		yes
9	22243	120	45013	NEW		yes
10	22243	161	45013	COM		yes
11	22243	161	45013	COM		yes
12	22243	170	45013	COM		No
13	22243	190	45013	NEW		No
14	22243	211	45013	NEW		No
15	22243	211	45013	NEW		No
16	22243	220	45013	NEW		No
17	22243	240	45013	NEW		No
18	22243	261	45013	NEW		No
19	22243	261	45013	NEW		No
20	22243	270	45013	NEW		No
21	22243	290	45013	NEW		No
Sheet4

Cell Formulas
Range	Formula
F2:F21	F2	=IF(B2<MAXIFS(B:B,A:A,A2,D:D,"<>New"),"yes","No")

That is working! The only question I have: In our data the work ticket and step number are text fields, which this doesn't work with. The "work around" is I created two columns that multipled those columns by 1 to make them a number. Do you have other suggestions?

 

[Fluff]

How about

=IF(--B2<MAX(IF(($A$2:$A$100=A2)*($D$2:$D$100<>"New"),--$B$2:$B$100)),"yes","No")