How can I reformat groups of data into columns of data?

[GrapeApe561]

Hello,
Today I have been given a task to reformat a massive, 36 sheet long excel file that has pretty messy organization. This is a screenshot of the original excel sheet: http://www.screencast.com/t/iVh0qWx5fCuk
And this is how the formatting should look like: http://www.screencast.com/t/bIb2NvDit
If you notice in the Before screenshot, Column comprises of sections of 7 cells, first cell is name, second cell is the date, and the 5 colored cells. And then the section repeats again, with a different name and address.
In the After screenshot, the date and the colored cells get their own columns, and the previous cells that they had are just filled with the person's name.
Can anybody PLEASE help me with this? I have no clue where to begin! Thank you so much!

 

[GrapeApe561]

Hello,
Today I have been given a task to reformat a massive, 36 sheet long excel file that has pretty messy organization. This is a screenshot of the original excel sheet: http://www.screencast.com/t/iVh0qWx5fCuk
And this is how the formatting should look like: http://www.screencast.com/t/bIb2NvDit
If you notice in the Before screenshot, Column comprises of sections of 7 cells, first cell is name, second cell is the date, and the 5 colored cells. And then the section repeats again, with a different name and address.
In the After screenshot, the date and the colored cells get their own columns, and the previous cells that they had are just filled with the person's name.
Can anybody PLEASE help me with this? I have no clue where to begin! Thank you so much!

Anybody know if I'm on the right track and provide some guidance please? I made a copy of the original spreadsheet, then insert ed three columns at the beginning placing these formulas in row 4 Col A =IF("F"=LEFT(F4,1),D4,A3) Col B =IF("F"=LEFT(F4,1),E4,B3) Col C =IF(D5="",C3,IF(TYPE(D5)=1,D5,C3)).

Thank you for your help and input!