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

 

[Fluff]

How about

=IF(COUNTIFS(A:A,A2)>COUNTIFS(A$2:A2,A2),"Yes","No")

 

[Pestomania]

How about

=IF(COUNTIFS(A:A,A2)>COUNTIFS(A$2:A2,A2),"Yes","No")

I couldn't get this one to work or how to fix it if I was messing it up. But I did notice in my data table, the "Date Updated" is pretty much not helpful.

I have added a different form of the same table. Essentially, it identifies the last step that has a status code of "Not New". In this case, the greatest step is 00170. It then says "yes it is less than 170 and to review"

Book1
	A	B	C	D	E	F
1	WorkTicketNo	StepNo	DateUpdated	StatusCode	Max Step Scanned	Is this Step less than 170?
2	000000022243	000010	3/28/2023 0:00	NEW	170	Yes
3	000000022243	000031	3/28/2023 0:00	COM	170	Yes
4	000000022243	000031	3/28/2023 0:00	COM	170	Yes
5	000000022243	000070	3/28/2023 0:00	COM	170	Yes
6	000000022243	000091	3/28/2023 0:00	COM	170	Yes
7	000000022243	000091	3/28/2023 0:00	COM	170	Yes
8	000000022243	000100	3/28/2023 0:00	COM	170	Yes
9	000000022243	000120	3/28/2023 0:00	NEW	170	Yes
10	000000022243	000161	3/28/2023 0:00	COM	170	Yes
11	000000022243	000161	3/28/2023 0:00	COM	170	Yes
12	000000022243	000170	3/28/2023 0:00	COM	170	No
13	000000022243	000190	3/28/2023 0:00	NEW	170	No
14	000000022243	000211	3/28/2023 0:00	NEW	170	No
15	000000022243	000211	3/28/2023 0:00	NEW	170	No
16	000000022243	000220	3/28/2023 0:00	NEW	170	No
17	000000022243	000240	3/28/2023 0:00	NEW	170	No
18	000000022243	000261	3/28/2023 0:00	NEW	170	No
19	000000022243	000261	3/28/2023 0:00	NEW	170	No
20	000000022243	000270	3/28/2023 0:00	NEW	170	No
21	000000022243	000290	3/28/2023 0:00	NEW	170	No
Sheet1

 

[Fluff]

Glad you sorted it & thanks for letting us know.

 

[Pestomania]

Glad you sorted it & thanks for letting us know.

Sorry, I manually typed these in. I am trying to identify how to complete this using a formula. That was a confusing statement, my apologies.

 

[Fluff]

Do you have col E & just looking for col F?

 

[Pestomania]

Do you have col E & just looking for col F?

Both are manual. I do not have either.

 

[Fluff]

Then how do you calculate the the max step is 170?

 

[Pestomania]

Then how do you calculate the the max step is 170?

The max step is the maximum step number which has a statuscode that does not equal new. The above data set is only one work ticket which in reality will be thousands in the table.

In the information above, step 170 has a status code of "COM" and everything greater is set to "New" which we do not want to consider.

If only dealing with one work ticket, you could consider:

Book1
	A	B	C	D	E
27	000000022243	000010	NEW		Yes
28	000000022243	000031	COM	22243000031	Yes
29	000000022243	000031	COM	22243000031	Yes
30	000000022243	000070	COM	22243000070	Yes
31	000000022243	000091	COM	22243000091	Yes
32	000000022243	000091	COM	22243000091	Yes
33	000000022243	000100	COM	22243000100	Yes
34	000000022243	000120	NEW		Yes
35	000000022243	000161	COM	22243000161	Yes
36	000000022243	000161	COM	22243000161	Yes
37	000000022243	000170	COM	22243000170	No
38	000000022243	000190	NEW		No
39	000000022243	000211	NEW		No
40	000000022243	000211	NEW		No
41	000000022243	000220	NEW		No
42	000000022243	000240	NEW		No
43	000000022243	000261	NEW		No
44	000000022243	000261	NEW		No
45	000000022243	000270	NEW		No
46	000000022243	000290	NEW		No
Sheet1

Cell Formulas
Range	Formula
D27:D46	D27	=IF(C27<>"New",CONCATENATE(A27,B27)*1,"")
E27:E46	E27	=IF(CONCATENATE(A27,B27)*1<MAX($D$27:$D$46),"Yes","No")

In this table, it is identifying the "concatenated fields" of each row and then identifies if the current row is less than the "max" concatenated field.

I am unfortunately not great with the match and index formulas to be able to run this on thousands of individual work tickets in the table to tell it to only look at the work tickets that match the current column A row.

 

[Fluff]

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")