helprb123
08-06-2014, 10:08 AM
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
Line Item
Work Item
Description
Work Order
Completion Date
System
Account Code
Breakout
Budget
Actual
Car
Location
Type
Labor
1
2
Materials
11
12
006A
077-01
Transmission
123
7/2
AB
BB
Total
12
14
1
CA
DA
Labor
2
3
Materials
10
9
006A
077-02
Motor
124
7/1
AC
BB
Total
12
12
1
CB
DB
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
Line Item
Work Item
Description
Work Order
Completion Date
System
Account Code
Budget - Labor
Budget - Materials
Budget - Total
Actual - Labor
Actual - Materials
Actual - Total
Car
Location
Type
006A
077-01
Transmission
123
7/2
AB
BB
1
11
12
2
12
14
1
CA
DA
006A
077-02
Motor
124
7/1
AC
BB
2
10
12
3
9
12
1
CB
DB
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! I've also attached an image of my spreadsheet to view it better. Table 1 is the same format as Part B and table 2 is the same format as Part A.
-Brooke
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
Line Item
Work Item
Description
Work Order
Completion Date
System
Account Code
Breakout
Budget
Actual
Car
Location
Type
Labor
1
2
Materials
11
12
006A
077-01
Transmission
123
7/2
AB
BB
Total
12
14
1
CA
DA
Labor
2
3
Materials
10
9
006A
077-02
Motor
124
7/1
AC
BB
Total
12
12
1
CB
DB
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
Line Item
Work Item
Description
Work Order
Completion Date
System
Account Code
Budget - Labor
Budget - Materials
Budget - Total
Actual - Labor
Actual - Materials
Actual - Total
Car
Location
Type
006A
077-01
Transmission
123
7/2
AB
BB
1
11
12
2
12
14
1
CA
DA
006A
077-02
Motor
124
7/1
AC
BB
2
10
12
3
9
12
1
CB
DB
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! I've also attached an image of my spreadsheet to view it better. Table 1 is the same format as Part B and table 2 is the same format as Part A.
-Brooke