title: Merge-Delete Macro for Excel
body: <p>I have an excel spreadsheet containing transcripts from various television shows.  As you can see, each time the speaker changes, they are introduced as "BURNETT:" or "HANNITY:".  I need a way to merge all the cells that follow a particular speaker so that their speech is all contained within one Excel cell.  I tried text-to-columns saving as a CSV and doing a find-replace, but of course that didn't work.</p>
  
  <p>For instance, I need this...</p>
  
  <pre><code>NETWORK SHOW    DATE    TIME    TIMEBLOCK   SPEAKER SPEAKTURN
  CNN OUTFRONT    2011-12-05  19:00   19:00           ERIN BURNETT OUTFRONT
  CNN OUTFRONT    2011-12-05  19:00   19:00           ERIN BURNETT, HOST: Thanks, John. We're live from the Middle East…
  CNN OUTFRONT    2011-12-05  19:00   19:00           Let's go OUTFRONT.
  CNN OUTFRONT    2011-12-05  19:00   19:00           I'm Erin Burnett and I'm OUTFRONT tonight live from Abu Dhabi in…
  CNN OUTFRONT    2011-12-05  19:00   19:00           Now Iran claims that it shot down an American drone that looks a…
  CNN OUTFRONT    2011-12-05  19:00   19:00           If this really happened, that means that technology is in the han…
  CNN OUTFRONT    2011-12-05  19:00   19:00           So far recently we've had Israel threatening to bomb Iranian nucl…
  CNN OUTFRONT    2011-12-05  19:00   19:00           Iran is defiant, and a defiant Iran could mean surging oil prices…
  CNN OUTFRONT    2011-12-05  19:00   19:00           Robin Mills is author of "The Myth of the Oil Crisis". Riad Kahwa…
  CNN OUTFRONT    2011-12-05  19:00   19:00           RIAD KAHWAJI, FOUNDER, INST. MILITARY ANALYSIS: Well, I think…
  CNN OUTFRONT    2011-12-05  19:00   19:00           BURNETT: Teetering.
  </code></pre>
  
  <p>To become this...</p>
  
  <pre><code>NETWORK SHOW    DATE    TIME    TIMEBLOCK   SPEAKER  SPEAKTURN
  CNN OUTFRONT    2011-12-05  19:00   19:00            ERIN BURNETT OUTFRONT
  CNN OUTFRONT    2011-12-05  19:00   19:00            ERIN BURNETT, HOST: Thanks, John. &lt;&lt;all the intervening text in the rows in between&gt;&gt; Robin Mills is author of "The Myth of the Oil Crisis." Riad Kahwa…
  CNN OUTFRONT    2011-12-05  19:00   19:00            RIAD KAHWAJI, FOUNDER, INST. MILITARY ANALYSIS: Well, I think…
  CNN OUTFRONT    2011-12-05  19:00   19:00            BURNETT: Teetering.
  </code></pre>
  
  <p>See how Erin Burnett's speaking turn is now contained within one cell and the other rows have been deleted?  I need a macro that does that.</p>
  
  <p>From there, I can make it look like this by using text-to-columns function and deleting all the rows that don't have a name in the SPEAKER column.  This would be the final goal, but I can do these steps (yeah, I know, leaving you the hard part).</p>
  
  <pre><code>NETWORK SHOW    DATE    TIME    TIMEBLOCK   SPEAKER                                               SPEAKTURN
  CNN OUTFRONT    2011-12-05  19:00   19:00   ERIN BURNETT, HOST                                    Thanks, John. &lt;&lt;intervening text&gt;&gt; Robin Mills is author of "The Myth of the Oil Crisis." Riad Kahwa…
  CNN OUTFRONT    2011-12-05  19:00   19:00   RIAD KAHWAJI, FOUNDER, INST. FOR MILITARY ANALYSIS    Well, I think…
  CNN OUTFRONT    2011-12-05  19:00   19:00   BURNETT                                               Teetering.
  </code></pre>
  
  <p>The worksheet, should you desire it, is available here on <a href="http://wikisend.com/download/643834/CleanMS.xlsx" rel="nofollow">Wikisend</a> for the next week.</p>
  
  <p>For what it's worth, I'm pretty sure that any script that recognizes a cell as introducing a new speaker would need to just see that there were at least four consecutive capital letters eventually followed a colon (I say eventually, because of the titles and occupations given the first time they speak).  Then it could look down the column until it finds another such cell.  It would then concatenate the contents (inserting spaces in between each) of all the cells between the "found" cells, and delete the rows that no longer had any SPEAKTURN data in them.</p>
  
  <p>Finally, I should apologize.  I know that the expectation on Stackoverflow is that I should do ample research,  but I am coming up on a deadline, and I don't know where else to turn.  I learned how to trip-stumble my way through R over the course of a month last semester, but as a Political Science major way out of my league, I can't for the life of me learn enough VBA to do this by Tuesday. :: hanging head in shame ::</p>
  
  <p>Without a macro to do this, I'll have to spend a several days merging more than 8,000 cells.  I'm desperate.  If you desire compensation---I'm a poor college senior trying to do a Communications senior project---I will do my best.  I'm just at my wits end here.</p>
  
  <p>PS If you want to know why I need this... WELL, I will be scoring each of the speaking turns for the presence or absence of 13 types of manipulative tactics by TV show hosts: (1) insulting language, (2) name calling, (3) emotional displays, (4) emotional language, (5) verbal fighting/sparring, (6) character assassination, (7) misrepresentative exaggeration, (8) mockery/sarcasm, (9) conflagration, (10) ideologically extremizing language, (11) slippery slope arguments, (12) belittling, and (13) obscene language.  It's all about how cable news shows get people riled up.  8000 rows of that will take a while, which is why I am so desperate for your help.</p>