Dirty Data to Clean Report


New Member
Mar 21, 2013
I am trying to learn how to take a bunch of data that repeats itself and turn into a nice clean presentable report. I am trying to create this so I can track all my projects stage-gate dates in one report. It would be nice to be able to create something to help me automate this process.

For example taking my data from the left side and turning it into the right side... I and my other teammates wont have time to do this for every project and every meeting so I need help!

Project Name</SPAN>Project Gate Name</SPAN>Project Gate Date</SPAN>Project Team Leader Name</SPAN>Project Sponsor</SPAN>Project</SPAN>VP</SPAN>Director</SPAN>Manager</SPAN>Media Start Date</SPAN>Go to Concept</SPAN>Go to D&B</SPAN>Go to Test and Recommend</SPAN>Go to Implement</SPAN>
Footballs</SPAN>Media Start Date</SPAN>5/3/2012</SPAN>Jodie</SPAN>Liz</SPAN>B-Balls</SPAN>Brad</SPAN>Kathy</SPAN>1/2/2013</SPAN>2/4/2013</SPAN>5/6/2013</SPAN>6/6/2013</SPAN>7/8/2013</SPAN>
Footballs</SPAN>Go to Concept</SPAN>7/30/2012</SPAN>Jodie</SPAN>Liz</SPAN>LaX Balls</SPAN>Kelly</SPAN>Jodie</SPAN>2/2/2012</SPAN>6/18/2012</SPAN>10/19/2012</SPAN>2/26/2013</SPAN>8/8/2013</SPAN>
Footballs</SPAN>Go to Design and Build</SPAN>8/30/2012</SPAN>Jodie</SPAN>Liz</SPAN>
Footballs</SPAN>Go to Test & Recommend</SPAN>11/7/2012</SPAN>Jodie</SPAN>Liz</SPAN>
Footballs</SPAN>Go to Implement</SPAN>4/18/2013</SPAN>Jodie</SPAN>Liz</SPAN>
B-Balls</SPAN>Go to Concept</SPAN>1/2/2013</SPAN>Kathy</SPAN>Brad</SPAN>
B-Balls</SPAN>Go to Design & Build</SPAN>2/4/2013</SPAN>Kathy</SPAN>Brad</SPAN>
B-Balls</SPAN>Go to Evaluate</SPAN>5/6/2013</SPAN>Kathy</SPAN>Brad</SPAN>
B-Balls</SPAN>Go to Implement</SPAN>6/6/2013</SPAN>Kathy</SPAN>Brad</SPAN>
B-Balls</SPAN>Go to Test & Recommend</SPAN>7/8/2013</SPAN>Kathy</SPAN>Brad</SPAN>
LaX Balls</SPAN>[EMPTY]</SPAN>1/1/1900</SPAN>Jodie</SPAN>Kelly</SPAN>
LaX Balls</SPAN>Media Start Date</SPAN>2/2/2012</SPAN>Jodie</SPAN>Kelly</SPAN>
LaX Balls</SPAN>Go to Concept</SPAN>6/18/2012</SPAN>Jodie</SPAN>Kelly</SPAN>
LaX Balls</SPAN>Go to Design and Build</SPAN>10/19/2012</SPAN>Jodie</SPAN>Kelly</SPAN>
LaX Balls</SPAN>Go to Test & Recommend</SPAN>2/26/2013</SPAN>Jodie</SPAN>Kelly</SPAN>
LaX Balls</SPAN>Go to Implement</SPAN>8/8/2013</SPAN>Jodie</SPAN>Kelly</SPAN>
LaX Sticks</SPAN>[EMPTY]</SPAN>1/1/1900</SPAN>Rodney</SPAN>Andrew</SPAN>
LaX Sticks</SPAN>Media Start Date</SPAN>1/1/1900</SPAN>Rodney</SPAN>Andrew</SPAN>
LaX Sticks</SPAN>Go to Concept</SPAN>6/1/2012</SPAN>Rodney</SPAN>Andrew</SPAN>
LaX Sticks</SPAN>Go to Design and Build</SPAN>6/3/2012</SPAN>Rodney</SPAN>Andrew</SPAN>
LaX Sticks</SPAN>Go to Test & Recommend</SPAN>4/18/2013</SPAN>Rodney</SPAN>Andrew</SPAN>
LaX Sticks</SPAN>Go to Implement</SPAN>10/1/2013</SPAN>Rodney</SPAN>Andrew</SPAN>


Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Welcome to the Board!

You should be able to use a Pivot Table:

Excel 2012
3Max of Project Gate DateProject Gate Name
4Project NameProject Team Leader NameMedia Start DateGo to ConceptGo to Design & BuildGo to Design and BuildGo to Test & RecommendGo to EvaluateGo to Implement
7LaX BallsJodie02/02/1206/18/1210/19/1202/26/1308/08/13
8LaX SticksRodney01/01/0006/01/1206/03/1204/18/1310/01/13

Just note that you don't show VP/Director/Manager designations in the data, so you won't be able to represent that.

If you PM me your e-mail address I'll send you this example.

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back