Dirty Data to Clean Report

[AndrewSimmons]

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>

[EMPTY]</SPAN>

1/1/1900</SPAN>

Jodie</SPAN>

Liz</SPAN>

Footballs</SPAN>

Liz</SPAN>

Jodie</SPAN>

5/3/2012</SPAN>

7/30/2012</SPAN>

8/30/2012</SPAN>

11/7/2012</SPAN>

4/18/2013</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>

[EMPTY]</SPAN>

1/1/1900</SPAN>

Kathy</SPAN>

Brad</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>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL span=9><COL><COL><COL><COL></COLGROUP>

 

[Smitty]

Welcome to the Board!

You should be able to use a Pivot Table:

Excel 2012
	A	B	C	D	E	F	G	H	I
3	Max of Project Gate Date		Project Gate Name
4	Project Name	Project Team Leader Name	Media Start Date	Go to Concept	Go to Design & Build	Go to Design and Build	Go to Test & Recommend	Go to Evaluate	Go to Implement
5	B-Balls	Kathy		01/02/13	02/04/13		07/08/13	05/06/13	06/06/13
6	Footballs	Jodie	05/03/12	07/30/12		08/30/12	11/07/12		04/18/13
7	LaX Balls	Jodie	02/02/12	06/18/12		10/19/12	02/26/13		08/08/13
8	LaX Sticks	Rodney	01/01/00	06/01/12		06/03/12	04/18/13		10/01/13
Sheet2

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.

HTH,