puzzle: recognizing colored cells in a list and copying to the side

I'm trying to solve this Excel puzzle. What I'm looking to do is recognize project names in a list, and assign them to the roles also in the list. There are a few elements to this puzzle:

1) Recognizing the project names as such. They are colored differently but that is the only difference. They don't change too often so it would be possible to refer to a static list of them, but I'd have to update that list. I'm told that it takes VBA to recognize cell colors.

2) Moving the project names to the relevant spots in another column.

I get the output from some software in the format of the first tab in the attached file. I'd like to make it look like the second tab so that I can make the pivot table shown on the third tab.

As for my Excel experience, I'm pretty good with Excel but new to macros/VBA. Thanks for any advice you can give!

Sample File.xlsx

http://www.excelforum.com/excel-gene...-the-side.html