rbexcelhelp123
New Member
- Joined
- Aug 6, 2014
- Messages
- 1
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
<tbody>
</tbody>
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
<tbody>
</tbody>
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
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 |
<tbody>
</tbody>
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 |
<tbody>
</tbody>
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
