Hello how to get a increment numbers for duplicate with array function.

Example in below picture if we search a word in entire A row if there is any duplicates it will add numbers in B.

CodePudding user response：

Try

=arrayformula(if(A1:A="",,
A1:A&if(countif(A1:A,A1:A)=1,,
"-"&text(countifs(A1:A,A1:A,row(A1:A),"<="&row(A1:A)),"00"))))

Or...

=A1&if(countif(A$1:A,A1)=1,,
"-"&text(countif(A$1:A1,A1),"00")) 

...and dragdown.

CodePudding user response：

Count duplicates

function countdups() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(1,1,sh.getLastRow(),2).getValues();
  let uA = [];
  let obj = {pA:[]};
  vs.forEach(r => {
    if(!~uA.indexOf(r[0])) {
      uA.push(r[0]);
      obj[r[0]] = 1;
      obj.pA.push(r[0]);
    } else {
      obj[r[0]]  = 1;
    }
  });
  let vo = obj.pA.map(p => [`${p}-${obj[p]}`]);
  sh.getRange(1,2,vo.length,1).setValues(vo);
}