Macro or VBA to Clean Up Messy Data

I received data that has come across a little out of order. Each row should include the following fields without any blanks:

Original Row, Index Number, Case, Date1, Action1 (Most cases will have more Date and Action columns).

I am trying to build a macro that will move the date and action cells to the correct positions. See below for example

Example1.jpg

Example 2.jpg

I have thousands of records of data that I need to do this for before I can begin my testing. The max number of Dates and Actions that one Index number has is 17 (Date17, Action17).

My thought process on what to do is below, however it is still flawed.

Begin in the first Index Number cell. Determine if there is text in the cell. If yes, move down one row and perform the same test. If there is no text, move to the right until a cell with text is selected, or until Column E (then return to Index Number Column and move down one row). Cut the selected cell if text was found. Move Up one cell. If there is data in this cell, move right one. If there is NO data in this cell, paste. If there is data, continue shifting to the right one cell until an empty space is identified. Return to the cell that had the text cut. Move to the right one cell until Column E and repeat the process.

I do not want to write over any of the data in the Index Number and Case Column, I only want to move the Date and Action Items beneath it up into the correct row.

I've attached a sample workbook to this thread.

Thanks!
Mike

We are all set actually! I didn't even realize it worked 100% and had just come to its logical end!! Thank you! +1 Rep