Gathering all the unique values from one column an

问题:

I have this form of a spreadsheet:

 A   B   C  D
abc abc abc 1
def ghi jkl 1
mno pqr stu 3
vwx yza bcd 4
mno pqr stu 5
mno pqr stu 5
vwx yza bcd 5
mno pqr stu 1

Where the first 3 columns are just data of type string. The column D has integers which has numbers repeating. My question is how to output a fifth column like so:

 A   B   C  D E
abc abc abc 1 1
def ghi jkl 1 3
mno pqr stu 3 4
vwx yza bcd 4 5
mno pqr stu 5
mno pqr stu 5
vwx yza bcd 5
mno pqr stu 1

It only outputs the unique numbers from column D.

I imagined running an if/else statement in a for or while loop that checks each cell in "D" and stores any value not previously "seen" in an array. Then outputting the array in column E.

I was wondering if there is a more efficient way to do this. Also the above is just a small example. Most likely the data range is in the 400 range. (Row wise. Columns are only 4 or 5 including the new output column.)

Thanks in advance.

P.S. I searched for this here but I'm only getting questions that relate to deleting duplicate rows. If there is a question that asks this already, please link me to it.

回答1:

here is a way to do that... probably not the only one but probably not bad...

I added a few logs to see intermediate results in the logger.

function keepUnique(){
  var col = 3 ; // choose the column you want to use as data source (0 indexed, it works at array level)
  var sh = SpreadsheetApp.getActiveSheet();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var data=ss.getDataRange().getValues();// get all data
  Logger.log(data);
  var newdata = new Array();
  for(nn in data){
    var duplicate = false;
    for(j in newdata){
      if(data[nn][col] == newdata[j][0]){
        duplicate = true;
      }
    }
    if(!duplicate){
      newdata.push([data[nn][col]]);
    }
  }
  Logger.log(newdata);
  newdata.sort(function(x,y){
  var xp = Number(x[0]);// ensure you get numbers
  var yp = Number(y[0]);
  return xp == yp ? 0 : xp < yp ? -1 : 1;// sort on numeric ascending
});
  Logger.log(newdata);
 sh.getRange(1,5,newdata.length,newdata[0].length).setValues(newdata);// paste new values sorted in column of your choice (here column 5, indexed from 1, we are on a sheet))
  }

---

EDIT :

Following Theodros answer, the spreadsheet formula is indeed an elegant solution, I never think about it but I should !!! ;-)

=SORT(UNIQUE(D1:D))

gives exactly the same result...

回答2:

You can do that inside google-spreadsheets with the UNIQUE function.
Here is the doc to all available functions.
(You find UNIQUE in the Filter category)

Most likely you want to insert into cell E1:

=UNIQUE(D1:D)

This will populate column E with the unique values from all of column D while preserving the order. Furthermore this will dynamically update and reflect all changes made to column D.

To do that from within google-apps-script:

SpreadsheetApp.getActiveSheet()
 .getRange("E1").setFormula("=UNIQUE(D1:D)");