Make array unique

  • A+
Category:Languages

I am trying to make pipe delimited cell values in a column unique

Example

if cell value of D3 = Aa Bb|HH|Aa Bb|HH

then after running arrMakeUnique_n() the cell value of D3 = Aa Bb|HH

And this works, but if cell value of D2 is the same as D3 then only cell value of D3 is made unique D2 is unchanged

i.e

If there are multiple cells with the same value only one cell is made unique the others remain unchanged

I can not get why, any help is appriciated

Thanks

function arrMakeUnique_n() {   arrMakeUnique(   'sheet1',   ["Header"],    "|",   "|"   ); }  function arrMakeUnique(shtName,cheaders, dilm1, dilm2) {   var sheet = SpreadsheetApp.getActive().getSheetByName(shtName);   var range = sheet.getDataRange();   var rangeValues = range.getValues();   var LR = sheet.getLastRow();    var hn =[];   var fhn = [];   for (var k = 0; k <= cheaders.length-1; k++) {         //Get column indexes from headers        hn[k] = HTN(shtName,cheaders[k]) - 1;    }     for (var j = 0; j <= cheaders.length-1; j++) {     var frRange = sheet.getRange(2, hn[j]+1, LR-1, 1);      var frValues = frRange.getValues();       // iterate through all cells in the selected range    for (var cellRow = 1; cellRow < LR; cellRow++) {          //Make array with split on | get unique with .getUnique() then Join with |         //Logger.log((rangeValues[cellRow][hn[j]].toString().split(frValues[cellRow]+dilm1)).getUnique().join(frValues[cellRow]+dilm2).trim())         rangeValues[cellRow][hn[j]] = (rangeValues[cellRow][hn[j]].toString().split(frValues[cellRow]+dilm1)).getUnique().join(frValues[cellRow]+dilm2).trim();            }   }    // Write back all values at once   range.setValues(rangeValues);  } 

Edit: It turns out that using: .getUnique caused strange behavior in that it interfered with other scripts that were independent of its use. xyz solution worked great luckily

//Make array unique Array.prototype.getUnique = function(){    var u = {}, a = [];    for(var i = 0, l = this.length; i < l; ++i){       if(u.hasOwnProperty(this[i])) {          continue;       }       a.push(this[i]);       u[this[i]] = 1;    }    return a; }   function HTN(shtName,cheader){   var headers = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(shtName).getDataRange().getValues().shift();   var colindex = headers.indexOf(cheader);   return colindex+1; } 

 


Here is another method:

function removeDuplicateFromDilmStr_n() { var t="Abb Baa|HHH|Abb Baa" Logger.log(removeDuplicateFromDilmStr(t,"|", "|")) }  function removeDuplicateFromDilmStr(str, dilm1, dilm2){ var arr = str.toString().split(dilm1) var outArray = [];  arr.sort(lowerCase); function lowerCase(a,b){ return a.toLowerCase()>b.toLowerCase() ? 1 : -1;// sort function that does not "see" letter case } var unique_array = arr.filter(function(elem, index, self) {     return index == self.indexOf(elem); });  unique_array = unique_array.join(dilm2).trim();  return unique_array } 

And then change this:

rangeValues[cellRow][hn[j]] = (rangeValues[cellRow][hn[j]].toString().split(frValues[cellRow]+dilm1)).getUnique().join(frValues[cellRow]+dilm2).trim(); 

to this:

rangeValues[cellRow][hn[j]] = removeDuplicateFromDilmStr(rangeValues[cellRow][hn[j]], "|","|"); 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: