Eliminate combination duplicates in google spreadsheet

Question

I'm still on combination business on a google spreadsheet, I have 2 columns, A and B, representing currency and its code and I want all the "conversion" combinations, in both ways.

I succeeded in writing the code, but, I want now to eliminate the duplicates : I mean, in the result, I will have "Convert Dollar to Euro", "Convert Euro to Dollar", "Convert Dollar to EUR", "Euro to USD", "EUR to USD" and "USD to EUR".

But, I will also have, for example, "Euro to euro".

How can I solve this in my code :

   function matrix() {
   var sheet = SpreadsheetApp.getActiveSheet();
   var range = 'Sheet1!B4:C19';
   var destID = '1kVhuTwVr80AScne9ijtlWs9YlDf5YkixIFVVbPjoX5E';
   var destcell = 'Sheet1!D27';

   var curr = SpreadsheetApp.getActiveSpreadsheet().getRange(range).getValues();
   var currConv = [];
   for (var i = 0; i < curr.length; i++)
  {
for ( var j = 0; j < curr.length; j++)

{

  currConv.push(['Convert ' + curr[i][0] + ' to ' + ' ' + curr[j][0]]);
  currConv.push(['Convert ' + curr[i][0] + ' to ' + ' ' + curr[j][1]]);
  currConv.push(['Convert ' + curr[i][1] + ' to ' + ' ' + curr[j][0]]);
  currConv.push(['Convert ' + curr[i][1] + ' to ' + ' ' + curr[j][1]]);
}


}           

var destRange = SpreadsheetApp.openById(destID).getRange(destcell).offset(0, 0, currConv.length);
destRange.setValues(currConv);
}

I tried to insert something like "i != j", but it gives me an error.

Thanks a lot for your help !

i and j are just integer indexes. you need to compare the array values with rules to detect duplicates that arent equal like euro vs EUR. — Zig Mandel

Alan Wells Alan Wells · Accepted Answer · 2015-07-22T16:09:43

First you need to find the index positions of all the duplicates. There are stackoverflow answers for finding duplicates in an array, like the following link:

stackoverflow - answer - find duplicates

I adapted the code at the above link for this case.

function removeDuplicates(arr) {
//Code adapted from SO https://stackoverflow.com/a/840849/2946873

  arr = [9, 9, 9, 111, 2, 3, 3, 3, 4, 4, 5, 7];

  var i,
      len=arr.length,
      out=[],
      obj={},
      hasItAlreadyBeenFound="",
      indexPositionsToDelete=[];

  for (i=0;i<len;i++) {
    //check if this value is already in the object
    hasItAlreadyBeenFound = obj[arr[i]];
    if (hasItAlreadyBeenFound!==undefined) {
      Logger.log('Index: ' + i + " is a dup!");
      indexPositionsToDelete.push(i);
    };

    obj[arr[i]]=0;
  }

  //This creates an array of which VALUES are duplicates.  NOT there index number.
  //For this "use case", this following section of code is not needed, but for the sake of demonstration I've left it in.
  for (i in obj) {
    out.push(i);
  }

  //return out;
  Logger.log('Output: ' + out);
  //Log what indexes to delete
  Logger.log('indexPositionsToDelete: ' + indexPositionsToDelete);

  //Delete the value in every cell that is a duplicate
  for (i=0;i<indexPositionsToDelete.length;i+=1) {
    //To Do - get the range - delete the cell value
    //Match the index value of the array to the row number, which may be different

  };
}

Eliminate combination duplicates in google spreadsheet

1 Answers