如何拆分和转置2列结果 [英] How to split and transpose results over 2 columns

问题描述

不知道如何甚至接近这一点。

  ColA（UniqID）ColB 
 41447.9162847222 Name1，Name2我如何完成以下表格： ，Name3，Name4 
 41447.9158912037 Name2，Name6，Name9 
  

我想结束：

  ColA B 
 41447.9162847222名称1 
 41447.9162847222名称2 
 41447.9162847222名称3 
 41447.9162847222 Name4 
 41447.9158912037 Name2 
 41447.9158912037 Name6 
 41447.9158912037 Name9 
  

我可以'不使用VLOOKUP或MATCH作为名称可能会在其他记录中复制，但ColA代码将始终是唯一的。

T

$ b

= ArrayFormula（TRANSPOSE（REGEXREPLACE（SPLIT（ CONCATENATE（SUBSTITUTE（，& B：B;，; CHAR（10）& A：A& CHAR（9）））; CHAR（10））;（。*？）\ t（ 。*）; {$ 1;$ 2}）））

缺点是所有列都被强制为使用此方法的文本字符串。有一些解决方法，但是这个公式会变得更加荒谬。

---

使用Google Apps脚本自定义函数的答案，它也修剪逗号分隔列表中的前后空格，按照注释中的请求：

  function advanceSplit（range1 ，range2）
 {
 var output = []，temp; 
 for（var i = 0，length = range1.length; i  {
 if（range1 [i] [0]）
 {
 var temp = range2 [i] [0] .split（，）; （变量j = 0，length2 = temp.length; j  {
 output.push（[range1 [i] [0]，temp [j] 。修剪（）]）; 
} 
} 
} 
返回输出; 
} 
  

，然后在电子表格单元格中调用：

= advancedSplit（A：A; B：B）

对于非常大的数据集，后一种方法（自定义函数）比前者具有更好的性能。

Not sure how to even approach this. How do I complete the following which has come from a form:

ColA (UniqID)       ColB
41447.9162847222    Name1, Name2, Name3, Name4
41447.9158912037    Name2, Name6, Name9

I would like to end up with:

ColA               B
41447.9162847222   Name1
41447.9162847222   Name2
41447.9162847222   Name3
41447.9162847222   Name4
41447.9158912037   Name2
41447.9158912037   Name6
41447.9158912037   Name9

I can't use VLOOKUP or MATCH as Names may be duplicated in other records but ColA code will always be unique.

T

解决方案

An answer using native spreadsheet functions:

=ArrayFormula(TRANSPOSE(REGEXREPLACE(SPLIT(CONCATENATE(SUBSTITUTE(", "&B:B;", ";CHAR(10)&A:A&CHAR(9)));CHAR(10));"(.*?)\t(.*)";{"$1";"$2"})))

Disadvantage is that all columns are coerced to text strings with this method. There are workarounds for this, but the formula would get even more ridiculous.

---

An answer using a Google Apps Script custom function, which also trims leading and trailing spaces in the comma-separated list, as per the request in comments:

function advanceSplit(range1, range2)
{
  var output = [], temp;
  for (var i = 0, length = range1.length; i < length; i++)
  {
    if (range1[i][0])
    {
      var temp = range2[i][0].split(",");
      for (var j = 0, length2 = temp.length; j < length2; j++)
      {
        output.push([range1[i][0], temp[j].trim()]);
      }
    }
  }
  return output;
}

and then invoke in a spreadsheet cell:

=advancedSplit(A:A;B:B)

My anecdotal observation is that for very large data sets, the latter method (custom function) has better performance than the former.

这篇关于如何拆分和转置2列结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！