Pivot Tables, Extracting Data, and possible VBA help?

[rbexcelhelp123]

Hi, I am working on an excel sheet and need some help.


I have data in the format of the table below. I have only shown 2 examples of the data, but I have 15+ various line items which all have different work items, descriptions, work orders, completion dates, etc.

Table 1
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Line Item[/TD]
[TD]Work Item[/TD]
[TD]Description[/TD]
[TD]Work Order[/TD]
[TD]Completion Date[/TD]
[TD]System[/TD]
[TD]Account Code[/TD]
[TD]Breakout[/TD]
[TD]Budget[/TD]
[TD]Actual[/TD]
[TD]Car[/TD]
[TD]Location[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Labor[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Materials[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]006A[/TD]
[TD]077-01[/TD]
[TD]Transmission[/TD]
[TD]123[/TD]
[TD]7/2[/TD]
[TD]AB[/TD]
[TD]BB[/TD]
[TD]Total[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]DA[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Labor[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Materials[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]006A[/TD]
[TD]077-02[/TD]
[TD]Motor[/TD]
[TD]124[/TD]
[TD]7/1[/TD]
[TD]AC[/TD]
[TD]BB[/TD]
[TD]Total [/TD]
[TD]12[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]CB[/TD]
[TD]DB[/TD]
[/TR]
</tbody>[/TABLE]


I need to sort the data in various methods to include the actual cost based on car, location, type, etc. I believe pivot tables is the best tool to use to analyze the data. However, with pivot tables, I've found I need all of the data on 1 line like shown below.

Table 2
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Line Item[/TD]
[TD]Work Item[/TD]
[TD]Description[/TD]
[TD]Work Order[/TD]
[TD]Completion Date[/TD]
[TD]System[/TD]
[TD]Account Code[/TD]
[TD]Budget - Labor[/TD]
[TD]Budget - Materials[/TD]
[TD]Budget - Total[/TD]
[TD]Actual - Labor[/TD]
[TD]Actual - Materials[/TD]
[TD]Actual - Total[/TD]
[TD]Car[/TD]
[TD]Location[/TD]
[TD]Type[/TD]
[/TR]
[TR]
[TD]006A[/TD]
[TD]077-01[/TD]
[TD]Transmission[/TD]
[TD]123[/TD]
[TD]7/2[/TD]
[TD]AB[/TD]
[TD]BB[/TD]
[TD]1[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]2[/TD]
[TD]12[/TD]
[TD]14[/TD]
[TD]1[/TD]
[TD]CA[/TD]
[TD]DA[/TD]
[/TR]
[TR]
[TD]006A[/TD]
[TD]077-02[/TD]
[TD]Motor[/TD]
[TD]124[/TD]
[TD]7/1[/TD]
[TD]AC[/TD]
[TD]BB[/TD]
[TD]2[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]3[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]1[/TD]
[TD]CB[/TD]
[TD]DB[/TD]
[/TR]
</tbody>[/TABLE]


I will collect all future data based on Table 2; however, I need to display the data in a format like Table 1.


I tried using VLookUp and the Match/index functions above, but I could not get it to work correctly.


Any help/ideas/etc? I would greatly appreciate it!


-Brooke