Summarizing data from long list to Master spreadsheet

[mileijit]

Hi,

I have a really long list of data that I'd like to summarize into a Master file.

The long list of data I have is like this:

Project Number	Project Name	Project Type	Project Status	Staff Name	Week Ending Date	Hrs	Base Cost Rate	TS Code
1	Alpha	Absence	On Going	Adam	01/11/2015	8	20	Core Time
1	Alpha	Absence	On Going	Barry	01/11/2015	8	25	Core Time
1	Alpha	Absence	On Going	Charlie	01/11/2015	8	30	Core Time
2	Beta	Holiday	On Going	Adam	01/09/2015	10	20	Core Time
2	Beta	Holiday	On Going	Adam	08/09/2015	20	20	Core Time
2	Beta	Holiday	On Going	Charlie	01/10/2015	5	30	Core Time
2	Beta	Holiday	On Going	Charlie	08/10/2015	0.5	30	Core Time
3	Gamma	Fee	Completed	Adam	25/10/2015	40	20	Core Time
3	Gamma	Fee	Completed	Adam	01/11/2015	30	20	Core Time
3	Gamma	Fee	Completed	Adam	08/11/2015	20	20	Core Time
3	Gamma	Fee	Completed	Adam	15/11/2015	25	20	Core Time
3	Gamma	Fee	Completed	Adam	22/11/2015	40	20	Core Time
3	Gamma	Fee	Completed	Adam	29/11/2015	17.5	20	Core Time
3	Gamma	Fee	Completed	Adam	06/12/2015	23	20	Core Time
3	Gamma	Fee	Completed	Adam	13/12/2015	40	20	Core Time
3	Gamma	Fee	Completed	Adam	20/12/2015	35	20	Core Time
4	Delta	Fee	On Going	David	25/10/2015	40	30	Core Time
4	Delta	Fee	On Going	David	01/11/2015	10	40	OT 1
4	Delta	Fee	On Going	David	08/11/2015	10	40	OT 1
4	Delta	Fee	On Going	David	29/11/2015	5	55	OT 2
4	Delta	Fee	On Going	David	06/12/2015	5	55	OT 2

<tbody>
</tbody>

What I'd like is to have a Master sheet automatically capture the following data:

Project Number	Project Name	Project Type	Project Status	September	October	November	December	Total Project Cost
1	Alpha	Absence	On Going	-	-	600	-	600
2	Beta	Holiday	On Going	600	165	-	-	765
3	Gamma	Fee	Completed	-	800	2,650	1,960	5,410
4	Delta	Fee	On Going	-	1,200	1,075	-	2,550
Total Monthly Costs				600	2,165	4,325	2,235	9,325

<tbody>
</tbody>

So basically, all the months are added together, and multiplied by the cost rate in the same row to create an overall project cost table.
After which I can sort and filter as required.

Any assistance would be greatly appreciated as I have no idea where to start with this!!!

 

[mileijit]

Hi Mick,

Thank you so much for the code. I've been playing around with it and have managed to get it working with this...

Sub Master()
Dim Rng As Range, Dn As Range, n As Long, c As Long, Ac As Long, Col As Long, Q As Variant
Dim Dic As Object, K As Variant
Set Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
Set Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare


For Each Dn In Rng.Offset(, 4)
    If Not Dic.exists(Month(Dn.Value)) Then
        Dic.Add Month(Dn.Value), MonthName(Month(Dn.Value))
    End If
Next
With Application
    ReDim mray(1 To Dic.Count)
        For n = .Min(Dic.keys) To .Max(Dic.keys)
            If Dic.exists(n) Then
                c = c + 1
                mray(c) = Dic(n)
            End If
        Next n
End With
ReDim ray(1 To Rng.Count + 1, 1 To 5 + Dic.Count)
            ray(1, 1) = "Project Number": ray(1, 2) = "Project Name"
            ray(1, 3) = "Project type": ray(1, 4) = "Project status": ray(1, UBound(ray, 2)) = "Total Project Cost"
        
        For n = 5 To 5 + Dic.Count - 1
            ray(1, n) = mray(n - 4)
        Next n
 Dic.RemoveAll
 n = 1
 For Each Dn In Rng
        For Ac = 5 To UBound(ray, 2) - 1
            If Month("1-" & ray(1, Ac) & "-2000") = Month(Dn.Offset(, 4).Value) Then Col = Ac
        Next Ac
        If Not Dic.exists(Dn.Value) Then
            n = n + 1
            ray(n, Col) = Dn.Offset(, 5).Value * Dn.Offset(, 6).Value
            ray(n, 1) = Dn.Offset(, -1).Value: ray(n, 2) = Dn.Value
            ray(n, 3) = Dn.Offset(, 1).Value: ray(n, 4) = Dn.Offset(, 2).Value
            Dic.Add Dn.Value, Array(n, ray(n, Col))
        Else
            Q = Dic.Item(Dn.Value)
            Q(1) = Q(1) + Dn.Offset(, 5).Value * Dn.Offset(, 6).Value
            ray(Q(0), Col) = ray(Q(0), Col) + Dn.Offset(, 5).Value * Dn.Offset(, 6).Value
            ray(Q(0), UBound(ray, 2)) = Q(1)
            Dic.Item(Dn.Value) = Q
        End If
    Next


n = n + 1
ray(n, 1) = "Total Monthly Costs"
For Ac = 5 To UBound(ray, 2)
    For Each K In Dic.keys
        ray(n, Ac) = ray(n, Ac) + ray(Dic(K)(0), Ac)
    Next K
Next Ac


With Sheets("Master").Range("A1").Resize(n, UBound(ray, 2))
    .NumberFormat = "#,##0"
    .Value = ray
    .Columns.AutoFit
    .Borders.Weight = 2
End With


End Sub

... However, it seems to miss out calculating the 'Total Project Cost' for some rows, and I can't figure out why!

---

I also have a separate sheet ("Invoices") that has more data I'd like to pull into the "Master" sheet.

The headings are: Invoice Number / Project Branch / Project Number* / Project Name* / Project Director / Transaction Date / Fees Net Amount / Consultancy Net Amount
*These have the same details as in the list pulled to compile the Master sheet.

I'd like to include the info from Project Branch and Project Director into columns in the Master Sheet (all projects will have the same value), just like the Project Name data.

Additionally, I'd like to calculate the sum of the Fees Net Amount and Consultancy Net Amount in the same fashion as the Total Project Cost column and insert them in the "Master" too.
Note: The Fees Net Amount and Consultancy Net Amount don't need to be broken up into monthly summaries (based on the Transaction Date column), but all the rows for X project need to be added together to give the Total amounts for each project.

I hope the above makes sense, and you can help with this as well!!!