How to split and transpose results over 2 columns

Question

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

Answer 1

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.