title: Text To Columns then Transpose
body: <p>I need to clean up a data dump that was exported to Excel.</p>
  
  <p>It is a file with groups and members in the groups from a directory. The problem is all of the members are in one cell, delimited by a semi-colon.</p>
  
  <p>What I think needs to be done is to create a macro that first does text-to-columns to separate the data into columns and then somehow insert enough rows to transpose the columns into rows.</p>
  
  <p>I want this:</p>
  
  <pre><code>+---+--------+---------------------+
  |   |   A    |          B          |
  +---+--------+---------------------+
  | 1 | Group1 | Name1; Name2; Name3 |
  | 2 | Group2 | Name1               |
  | 3 | Group3 | Name1; Name2        |
  +---+--------+---------------------+`
  </code></pre>
  
  <p>To look like this:</p>
  
  <pre><code>+---+--------+-------+
  |   |   A    |   B   |
  +---+--------+-------+
  | 1 | Group1 | Name1 |
  | 2 | Group1 | Name2 |
  | 3 | Group1 | Name3 |
  | 4 | Group2 | Name1 |
  | 5 | Group3 | Name1 |
  | 6 | Group3 | Name2 |
  +---+--------+-------+
  </code></pre>
  
  <p>These "name" cells can contain from 1 to 500 names.</p>
  
  <p><strong>Edit:</strong> While my question is similar to the one that was flagged there are differences that require different code. The other question involves parsing data from the first column into new rows and copying down the subsequent columns. My problem is sort of the opposite.</p>