2

I have a Sheet that looks like this:

╔═══════╦═════════════════════════════════════╦══╗
║  ID   ║                Users                ║  ║
╠═══════╬═════════════════════════════════════╬══╣
║ 12345 ║ bob@domain.com,su'uzan@domain.com   ║  ║
║ 99999 ║ steven@domain.com,rachel@domain.com ║  ║
╚═══════╩═════════════════════════════════════╩══╝

It's two column, one with an ID and one with comma-separated lists of emails. I need to look like this:

╔═══════╦════════════════════╦══╗
║  ID   ║       Users        ║  ║
╠═══════╬════════════════════╬══╣
║ 12345 ║ bob@domain.com     ║  ║
║ 12345 ║ su'uzan@domain.com ║  ║
║ 99999 ║ steven@domain.com  ║  ║
║ 99999 ║ rachel@domain.com  ║  ║
╚═══════╩════════════════════╩══╝

I've tried using pivot tables and some query operations but haven't been able to crack it. There's roughly 6,000 entries in the Sheet now. I also wasn't able to get anything from this question to work (I need something automatic, there's too much data to do manually.) This question also seems similar but is unanswered.

3

I modified the excellent code provided by Jacob Jan Tuinstra to fit your needs assuming your data starts at A2.

Step 1

Open the Script editor from your sheet menu and paste this code:

function result(range) {
  var output2 = [];
  for(var i = 0, iLen = range.length; i < iLen; i++) {
    var s = range[i][1].split(",");    
    for(var j = 0, jLen = s.length; j < jLen; j++) {
      var output1 = []; 
      for(var k = 0, kLen = range[0].length; k < kLen; k++) {
        if(k == 1) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
} 

Step 2

Paste anywhere (D1 or G1 or...) this formula: =result(A2:B5)

Step 3

Done

...wasn't able to get anything from this answer to work I need something automatic...

I believe it doesn't get more automatic.

If for any reason you cannot get it to work, please share the link to a test sheet in a comment so we can work it out.

  • Have you tried the solution offered? Did it work for you? If not we should try finding out why. If yes, as per site guidelines when an answer addresses your question, you should accept it and maybe upvote it so others can benefit as well. – marikamitsos Sep 28 at 2:54
  • I was able to modify this to get it to work, thanks! – Ian Hyzy Oct 3 at 3:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.