从一列收集所有唯一值并将它们输出到另一列中..? [英] 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

其中前 3 列只是字符串类型的数据.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. 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))
  }

<小时>

按照 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屋！