PDA

View Full Version : Extracting data, pivot tables, and maybe VBA help?



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

ranman256
08-06-2014, 10:13 AM
Yes, pivot is the way to go. You can format headers after the pivot. (record the pivot for later use)

helprb123
08-06-2014, 11:05 AM
What do you mean record the pivot for later use?

Also, how would I be able to display the data presented in part A in a format similar to part b?

Thanks!

apo
08-06-2014, 11:50 PM
Hi...

Can you attach a Workbook (rather than a screenshot) that contains both Part A (raw data) and Part B (desired Result).

Bob Phillips
08-07-2014, 12:47 AM
If I read this correctly, you are going to switch to the latter format and want a report in the former format. I think you might struggle to get a pivot to do it exactly as you want, so here is some code to do it


Public Sub Reformat()
Dim lastrow As Long
Dim i As Long

Application.ScreenUpdating = False

With ActiveSheet

lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = lastrow - 1 To 1 Step -1

.Rows(i + 1).Resize(2).Insert
.Cells(i + 1, "I").Value = .Cells(i + 3, "H").Value
.Cells(i + 2, "I").Value = .Cells(i + 3, "I").Value
.Cells(i + 3, "I").Value = .Cells(i + 3, "J").Value
.Cells(i + 1, "J").Value = .Cells(i + 3, "K").Value
.Cells(i + 2, "J").Value = .Cells(i + 3, "L").Value
.Cells(i + 3, "J").Value = .Cells(i + 3, "M").Value
.Cells(i + 1, "H").Value = "Labor"
.Cells(i + 2, "H").Value = "Materials"
.Cells(i + 3, "H").Value = "Total"
Next i

.Range("H1:J1").Value = Array("Breakout", "Budget", "Actual")
.Columns("K:M").Delete
End With

Application.ScreenUpdating = True
End Sub

snb
08-07-2014, 01:33 AM
If table 2 in sheet2; if table 1 should appear in sheet1:


Sub M_snb()
sn = Sheet2.Cells(1).CurrentRegion.Resize(Sheet1.Cells(1).CurrentRegion.Rows.Cou nt + 1)
sp = Application.Index(sn, Application.Transpose(Split(Join(Evaluate("transpose(row(1:" & UBound(sn) - 1 & "))"), "," & UBound(sn) & "," & UBound(sn) & ","), ",")), Array(1, 2, 3, 4, 5, 6, 7, 10, 13, 14, 15, 16))

For j = 2 To UBound(sn) - 1
sp(2 + 3 * (j - 2), 7) = "Labor"
sp(3 + 3 * (j - 2), 7) = "Materials"
sp(2 + 3 * (j - 2), 8) = sn(j, 8)
sp(3 + 3 * (j - 2), 8) = sn(j, 9)
sp(2 + 3 * (j - 2), 9) = sn(j, 11)
sp(3 + 3 * (j - 2), 9) = sn(j, 12)
Next

sheet1.Cells(1).Resize(UBound(sp), UBound(sp, 2)) = sp
End Sub

helprb123
08-07-2014, 07:13 AM
Attached is the workbook. I have the information in sheet 1. I need to format the information to be like sheet 2.

Thank you for your help.

Also, I tried the VBA code that two of you presented. Unfortunately, I could not get it to work correctly. Any thoughts on where I may be doing it incorrectly?

Thanks!

Bob Phillips
08-07-2014, 08:14 AM
Which one do you want to be transformed, the one on Sheet1 or the one on Sheet2?

snb
08-07-2014, 09:18 AM
see the attachment.

Macro in codemodule of 'thisworkbook'.

helprb123
08-07-2014, 09:28 AM
I actually need it to go both ways.

I currently have the data like in sheet 1 and need to convert to sheet 2.

In the future, I will have the data like in sheet 2 and need to convert to sheet 1.

Sorry for all of the confusion.

helprb123
08-07-2014, 09:33 AM
Thank you! This helps a ton. However, I still have one issue - the breakout names and account code column are the same. I need these separated. What part of the code would I change?