Collect all unique values ​​from one column and display them in another column ..?

Question

I have this form of a spreadsheet:

ABCD 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 

If the first 3 columns represent only the data of the type string. Column D has integers, the number of which is repeated. My question is how to output the fifth column as follows:

  ABCDE 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 outputs only unique numbers from column D.

I suggested running the if / else statement in a for or while loop, which checks each cell in "D" and stores any value that was not previously "visible" in the array. Then the output of the array to column E.

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

Thanks in advance.

PS I searched for it here, but I have questions related to deleting duplicate rows. If you have a question that asks about it, write to me.

Answer 1

here is a way to do it ... maybe not the only one, but probably not bad ...

I have added several journals to see intermediate results in the journal.

 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:

After Theodros answer the spreadsheet formula is really an elegant solution, I never think about it, but I have to !; -)

 =SORT(UNIQUE(D1:D)) 

gives exactly the same result ...

Answer 2

You can do this inside google tables using the UNIQUE function.
Here is a document for all the features available .
(You will find UNIQUE in the Filter category)

Most likely you want to insert into cell E1 :

 =UNIQUE(D1:D) 

This will fill column E unique values ​​from all columns of D , while maintaining order. In addition, it will dynamically update and reflect all changes made in column D

To do this from google-apps-script :

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