Extracting data, pivot tables, and maybe VBA help?

[helprb123]

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]

Yes, pivot is the way to go. You can format headers after the pivot. (record the pivot for later use)

[helprb123]

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]

Hi...

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

[Bob Phillips]

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]

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.Count + 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]

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]

Which one do you want to be transformed, the one on Sheet1 or the one on Sheet2?

[snb]

see the attachment.

Macro in codemodule of 'thisworkbook'.

[helprb123]

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]

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?