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:
[TABLE="class: grid, width: 650, align: left"]
<tbody>[TR]
[TD]Project Number[/TD]
[TD]Project Name[/TD]
[TD]Project Type[/TD]
[TD]Project Status[/TD]
[TD]Staff Name[/TD]
[TD]Week Ending Date[/TD]
[TD]Hrs[/TD]
[TD]Base Cost Rate[/TD]
[TD]TS Code[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Alpha[/TD]
[TD]Absence[/TD]
[TD]On Going[/TD]
[TD]Adam[/TD]
[TD]01/11/2015[/TD]
[TD]8[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Alpha[/TD]
[TD]Absence[/TD]
[TD]On Going[/TD]
[TD]Barry[/TD]
[TD]01/11/2015[/TD]
[TD]8[/TD]
[TD]25[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Alpha[/TD]
[TD]Absence[/TD]
[TD]On Going[/TD]
[TD]Charlie[/TD]
[TD]01/11/2015[/TD]
[TD]8[/TD]
[TD]30[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Beta[/TD]
[TD]Holiday[/TD]
[TD]On Going[/TD]
[TD]Adam[/TD]
[TD]01/09/2015[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Beta[/TD]
[TD]Holiday[/TD]
[TD]On Going[/TD]
[TD]Adam[/TD]
[TD]08/09/2015[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Beta[/TD]
[TD]Holiday[/TD]
[TD]On Going[/TD]
[TD]Charlie[/TD]
[TD]01/10/2015[/TD]
[TD]5[/TD]
[TD]30[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Beta[/TD]
[TD]Holiday[/TD]
[TD]On Going[/TD]
[TD]Charlie[/TD]
[TD]08/10/2015[/TD]
[TD]0.5[/TD]
[TD]30[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD]25/10/2015[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD]01/11/2015[/TD]
[TD]30[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD]08/11/2015[/TD]
[TD]20[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD]15/11/2015[/TD]
[TD]25[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD]22/11/2015[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD]29/11/2015[/TD]
[TD]17.5[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD]06/12/2015[/TD]
[TD]23[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD]13/12/2015[/TD]
[TD]40[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD]20/12/2015[/TD]
[TD]35[/TD]
[TD]20[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]David[/TD]
[TD]25/10/2015[/TD]
[TD]40[/TD]
[TD]30[/TD]
[TD]Core Time[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]David[/TD]
[TD]01/11/2015[/TD]
[TD]10[/TD]
[TD]40[/TD]
[TD]OT 1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]David[/TD]
[TD]08/11/2015[/TD]
[TD]10[/TD]
[TD]40[/TD]
[TD]OT 1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]David[/TD]
[TD]29/11/2015[/TD]
[TD]5[/TD]
[TD]55[/TD]
[TD]OT 2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]David[/TD]
[TD]06/12/2015[/TD]
[TD]5[/TD]
[TD]55[/TD]
[TD]OT 2[/TD]
[/TR]
</tbody>[/TABLE]

What I'd like is to have a Master sheet automatically capture the following data:
[TABLE="class: grid, width: 650, align: left"]
<tbody>[TR]
[TD]Project Number[/TD]
[TD]Project Name[/TD]
[TD]Project Type[/TD]
[TD]Project Status[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[TD]Total Project
Cost[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Alpha[/TD]
[TD]Absence[/TD]
[TD]On Going[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]600[/TD]
[TD]-[/TD]
[TD]600[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Beta[/TD]
[TD]Holiday[/TD]
[TD]On Going[/TD]
[TD]600[/TD]
[TD]165[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]765[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]-[/TD]
[TD]800[/TD]
[TD]2,650[/TD]
[TD]1,960[/TD]
[TD]5,410[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]-[/TD]
[TD]1,200[/TD]
[TD]1,075[/TD]
[TD]-[/TD]
[TD]2,550[/TD]
[/TR]
[TR]
[TD]Total Monthly Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]600[/TD]
[TD]2,165[/TD]
[TD]4,325[/TD]
[TD]2,235[/TD]
[TD]9,325[/TD]
[/TR]
</tbody>[/TABLE]



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!!!