Nosparks has provided a solution to convert the data from the format you gave us initially to the format you asked for. I realize you may not know how to implement the solution. The steps to implement his solution are:
First, open the worksheet containing your information (so you can save the macro code in that worksheet)
1. Create a macro and give it a name (such as "ReformatData") and a ctrl-letter key combination (such as ctrl-r, for ReformatData)
2. Select cell A1 by clicking in it (to give the macro a line of code)
3. Stop the macro recording.
4. Edit the macro (Tools->Macro->(Click macro name)->Edit)
5. Delete the line that says: Range("A1").Select
6. Paste the code from nospark's post into the same line (If you named your macro "ReformatData", don't include the first line of nosparks macro, the "Sub ReformatData()" line:
7. Close the macro editor by clicking the "X" in the upper right. The macro will be saved automatically when closed.
8. At this point, you should be able to run your macro. Hit Ctrl-r to generate your results.
If this is unclear or doesn't work for you, let us know so we can clear it up for you.