[FIXED] How to get the last row per group of category?

Issue

I want to label the last column with value 1 for the last record for each jobID, Product, Target. Please refer to table below.

JobID	Product	Target	Expected Output
1	ABC1	0
1	ABC1	0
1	ABC1	0
1	ABC1	0	1
2	ABC2	2
2	ABC2	2
2	ABC2	2
2	ABC2	2	1
3	ABC3	1
3	ABC3	1
3	ABC3	1
3	ABC3	1	1

The target will always remain the same per jobid and product so it is basically repeating. I need to label the value 1 as shown in expected output for the last record per JobID, Product and Target. How can I achieve this in google sheets?

Solution

try the reverse search feature of XMATCH:

=INDEX(IFERROR(1/(1/LAMBDA(x, XMATCH(x, x,,-1)=SEQUENCE(ROWS(x)))(A2:A)*1)))

or:

=INDEX(IF(A2:A="",,IF(COUNTIFS(A2:A, A2:A, ROW(A2:A), ">="&ROW(A2:A))=1, 1, )))

Answered By - player0

Answer Checked By - Marie Seifert (PHPFixing Admin)