从一列中收集所有唯一值,然后将它们输出到另一列中. [英] Gathering all the unique values from one column and outputting them in another column..?
问题描述
我有这种形式的电子表格:
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
前三列只是字符串类型的数据. D列具有整数,该整数具有重复的数字.我的问题是如何像这样输出第五列:
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
它仅输出D列中的唯一编号.
It only outputs the unique numbers from column D.
我想象过在for或while循环中运行if/else语句,该语句检查"D"中的每个单元格并将以前未看到"的任何值存储在数组中.然后在E列中输出数组.
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.
我想知道是否有更有效的方法来做到这一点.以上只是一个小例子.数据范围最有可能在400范围内. (明智的做法是,列只有4或5,其中包括新的输出列.)
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.)
谢谢.
P.S. 我在这里搜索,但是我只收到与删除重复行有关的问题.如果已经有一个问题要问这个,请链接到我.
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.
推荐答案
这是一种实现方法...可能不是唯一的方法,但可能还不错...
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 :
在Theodros回答之后,电子表格公式的确是一个优雅的解决方案,我从没考虑过,但我应该! ;-)
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...
这篇关于从一列中收集所有唯一值,然后将它们输出到另一列中.的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!