Gathering all the unique values from one column and outputting them in another column..?

Question

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.

Retort

Answer 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...

Ulick

Thanks again. This code also worked flawlessly and quickly. One question is what is that syntax for the "for loop"..? (The nn in data part.) I always thought you had to do something like this: (nn=0; nn<=data.length; nn++) I tried to find documentation for this format, but keep hitting a wall. Your format would shorten a lot of my older scripts. Thanks again! :) – Retort

this is just a shortend form, usually it includes the var declaration like this : for(var nn in data){... but the 'normal way does pretty much the same, in this case it would have been for(var nn=0;nn<data.length;nn++){ and not <= because we start from 0 and data length gives the length starting at 1 ;-). Note that some JS experts say that the short form can bring some issues in some situation but this is beyond my knowledge... personally I've never met any issue. – Ulick

have a look at this post on the same subject ;-) – Ulick

Yeah, I actually went on a research binge on that subject after you interested me with it. I never knew there was so much intricacy in even a simple 'for' loop. – Retort

Answer 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)");

Denaedenarius

Hmm... That seems devilishly too simple! Thanks! I'm betting that something like Serge's code above is ultimately behind this UNIQUE function. – Retort

Is there any way to use a formula to return the values in your script rather than setting it in the spreadsheet? I have a use case where I only need the data in the script and don't need it to be placed into the the sheet. – Oospore

@Oospore If you find any answers to the question you just asked that if we can store the returned values into an array, kindly do me a favor and post it somewhere. It will help me a ton. Thanks!! – Padre

Answer 3

Currently, in V8 engine, the easiest way to do this is to use Set:

/**
 * @returns {Object[]} Gets unique values in a 2D array
 * @param {Object[][]} array2d
 * @private
 */
const getUnique_ = array2d => [...new Set(array2d.flat())];

/**
 * Gets Values from a column, makes it unique and sets the modified values
 *   to the next column
 * @param {string} sheetName
 * @param {number} column Number of the column to uniquify
 * @param {number} headers Number of headers
 * @returns void
 */
const uniquifyAColumn = (sheetName = 'Sheet1', column = 3, headers = 1) => {
  const sh = SpreadsheetApp.getActive().getSheetByName(sheetName),
    rg = sh.getRange(1 + headers, column, sh.getLastRow() - headers, 1),
    values = rg.getValues(),
    uniqueValues = getUnique_(values).map(e => [e]);
  rg.offset(0, 1, uniqueValues.length).setValues(uniqueValues);
};

Carve

Using map works well, but I include checks to ensure that the start row and rows to search are valid before getting the range. – Sambar

Answer 4

Here's a script for getting a unique list of nonempty values in a column given the Sheet it's on and the header location of the column using an array to store the values and indexOf to find duplicates. You can then write the array wherever you'd like.

headerLocation is a Location object:

var Location = function(sheet, row, col) {
  this.sheet = sheet;
  this.row = row;
  this.col = col;
  this.addRow = function() { this.row = this.row + 1; }
  this.addCol = function() { this.col = this.col + 1; }
  this.getValue = function() { return sheet.getRange(this.row, this.col).getValue(); }
  this.toString = function() { return "(" + this.row + "," + this.col + ")"; }
}

This is the function to read the column and return unique values:

/**
* Get unique values in column, assuming data starts after header
* @param {Sheet} sheet - Sheet with column to search
* @param {object} headerLocation - row and column numbers of the column header cell
* @returns {array} list of unique values in column
*/
function getUniqueColumnValues(sheet, headerLocation) {
  let startRow = headerLocation.row + 1;
  let lastRow = sheet.getLastRow();
  let values = [];
  for (i = startRow ; i <= lastRow ; i++) {
    let value = sheet.getRange(i, headerLocation.col).getValue();
    if ((value != "") && (values.indexOf(value) == -1)) {
      values.push(value);
    }
  }  
  return values;
}

Or, using a Location to find the values:

function getUniqueColumnValues(sheet, headerLocation) {
  let values = [];
  let searchLocation = new Location(sheet, headerLocation.row + 1, headerLocation.col);
  let lastRow = sheet.getLastRow();
  while (searchLocation.row <= lastRow) {
    let value = searchLocation.getValue();
    if (values.indexOf(value) == -1) {
      values.push(value);
    }
    searchLocation.addRow();
  }
  return values;
}

Sambar