Normalize and Format data for Pivot Table

Question

Trying to take the data below and format it to use in a pivot table. Basically making two columns to insert the Customer column and the other column to show each of the workers. 

Below is current data format in Excel:

Date	Service Item	Description	Status	Billable	Total
Customer : Acme Corporation:Fourchon
Worker : Delatte, Lane M
8/29/2012	Dispatcher Services	Pending		0.5
8/30/2012	Dispatcher Services	Pending		1
8/31/2012	Dispatcher Services	Pending		1
8/31/2012	Mileage	Golden Meadow to Fourchon	Pending		35
9/1/2012	Dispatcher Services	Pending		1
9/2/2012	Dispatcher Services	Pending		1
9/3/2012	Dispatcher Services	Pending		1
9/4/2012	Dispatcher Services	Pending		1
9/5/2012	Dispatcher Services	Pending		0.5
9/5/2012	Mileage	Fourchon to Golden Meadow	Pending		35
Total for Delatte, Lane M  :	77
Worker : Estay, Justin P
8/29/2012	Dispatcher Services	Pending		0.5
8/30/2012	Dispatcher Services	Pending		1
8/31/2012	Dispatcher Services	Pending		1
8/31/2012	Mileage	LaRose to Fourchon	Pending		38
9/1/2012	Dispatcher Services	Pending		1
9/2/2012	Dispatcher Services	Pending		1
9/3/2012	Dispatcher Services	Pending		1
9/4/2012	Dispatcher Services	Pending		1
9/5/2012	Dispatcher Services	Pending		0.5
9/5/2012	Mileage	Fourchon to LaRose	Pending		38
Total for Estay, Justin P  :	83

Trying to achieve the following file format by adding Customer column and Worker column and copying information down:

Customer	Worker	Date	Service Item	Description	Status	Billable	Total
Acme Corporation:Fourchon	Delatte, Lane M	8/29/2012	Dispatcher Services	Pending		0.5
Acme Corporation:Fourchon	Delatte, Lane M	8/30/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	8/31/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	8/31/2012	Mileage	Golden Meadow to Fourchon	Pending		35
Acme Corporation:Fourchon	Delatte, Lane M	9/1/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	9/2/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	9/3/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	9/4/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	9/5/2012	Dispatcher Services	Pending		0.5
Acme Corporation:Fourchon	Delatte, Lane M	9/5/2012	Mileage	Fourchon to Golden Meadow	Pending		35
Acme Corporation:Fourchon	Delatte, Lane M	8/29/2012	Dispatcher Services	Pending		0.5
Acme Corporation:Fourchon	Delatte, Lane M	8/30/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	8/31/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	8/31/2012	Mileage	LaRose to Fourchon	Pending		38
Acme Corporation:Fourchon	Delatte, Lane M	9/1/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	9/2/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	9/3/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	9/4/2012	Dispatcher Services	Pending		1
Acme Corporation:Fourchon	Delatte, Lane M	9/5/2012	Dispatcher Services	Pending		0.5
Acme Corporation:Fourchon	Delatte, Lane M	9/5/2012	Mileage	Fourchon to LaRose	Pending		38

Answer 1

Sorry that logic is faulty

While column C is not blank

   If column C starts with "customer: ", move the rest to custname variable

   else

      If column C starts wtih "worker: " move the rest to worker variable

      else

         If column C starts wtih "Total for" delete the row

         else

             move custname variable to A column

             move worker variable to B column

             move focus down one row

Wend

Answer 2

Are they all on one worksheet or separate worksheets?

Is this a one time process? It may take only slightly longer to do it manually than to learn and test what you need to do to write a macro.

First you insert the 2 new blank columns, then you can define a macro to read through the rows and move the data as required

Assuming there are no blank rows as shown in your sample ...

The general logic could be something like this:

While column C is not blank

   If column C starts with "customer: ", move the rest to column a

   If column C starts wtih "worker: " move the rest to column b

   If column C starts wtih "Total for" delete the row

   move focus down one row

Wend

Answer 3

Hi,

Are there always 10 rows for each customer?

Answer 4

Your description is exactly what I want to do however through code versus manual cut and paste because it is a very large data set. The sample I gave was only 2 workers and I have 60 workers that I would have to manually do this for.  Thank you for your quick response and wondering if there is a solution to automate this with VBA or macro.

Answer 5

It appears that you are starting with a generated report that is equivalent to a "static" pivot table.

But if you want to generate your own pivot table, you have described most of the work required already. 

1. insert 2 new columns, "Customer" and "Worker"

2 Fill the columns using copy and paste with the right values

3 DELETE the existing total lines and values. These totals will be generated by the Pivot Table.  Probably easiest way is after adding and populating the columns do a sort on the total column to get all of the total rows in one place. Then you can delete all of the total rows easily at one time.

So what is your question, you seem to have answered it yourself already.