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.

score 19 · Answer 1 · answered Jun 30 '13 at 21:55

19

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

answered Jun 30 '13 at 21:55

tzelleke

15,023
5
33
49

Hmm... That seems devilishly too simple! Thanks! I'm betting that something like Serge's code above is ultimately behind this `UNIQUE` function. – PhysLabTsar Jul 01 '13 at 05:12
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. – greatwitenorth Aug 16 '18 at 16:00
@greatwitenorth 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!! – JustCurious Apr 06 '20 at 05:16

Serge insas · Accepted Answer · 2013-07-01T12:17:21.327

7

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

edited Jul 01 '13 at 12:17

answered Jun 28 '13 at 20:52

Serge insas

45,904
7
105
131

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! :) – PhysLabTsar Jun 29 '13 at 17:29
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 – Serge insas Jun 29 '13 at 19:53
have a look at [this post](http://stackoverflow.com/questions/17389477/error-on-string-after-adding-a-new-method-to-array-via-prototype) on the same subject ;-) – Serge insas Jun 30 '13 at 12:44
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. – PhysLabTsar Jul 01 '13 at 05:01

score 2 · Answer 3 · answered Oct 08 '20 at 17:04

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);
};

Using map works well, but I include checks to ensure that the start row and rows to search are valid before getting the range. — Janine White, Nov 13 '20 at 19:38

Janine White · Answer 4 · 2020-10-30T18:53:39.240

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;
}

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

4 Answers4

Linked

Related