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

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?

CodePudding user response：

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

CodePudding user response：

ALTERNATIVE:

Here's a script version if you want to explore App Script.

function myFunction() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet(); //gets the active sheet
  var range = sheet.getRange(2, 1, sheet.getLastRow(), 4); //gets A2:D13 dynamic to the number of rows
  var values = range.getValues(); //gets the value for the range

  for (var i = 0; i < values.length - 1; i  ) {
    
    if (values[i][0] == values[i   1][0]) {
      console.log('TRUE');
    } else {
      values[i][3] = 1;
    }
  }

CodePudding user response：

Use QUERY to group by Product and get the max row sequence corresponding to that group. Then MATCH the row sequence against group max to get the expected result:

=ARRAYFORMULA(LAMBDA(rg,seq,IFNA(MATCH(seq,QUERY({rg,seq},"Select max(Col2) group by Col1"),0)))(B2:B13,SEQUENCE(ROWS(B2:B13))))