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

 

[SteveO59L]

have you considered a pivot table?

 

[MickG]

Do you want all the Months of the year or only Sep to Dec ?????

 

[mileijit]

have you considered a pivot table?

Hi Steve,

I've played around with pivot tables, but I can't figure out how where to pout everything and how to get all the week ending calculations into a monthly summary!

I'm pretty much a clueless excel newbie with this much data to sort

 

[mileijit]

Do you want all the Months of the year or only Sep to Dec ?????

Hi Mick,

I'd only want the months that are listed - which is likely to increase as time goes on!

 

[oldbrewer]

Project Number	Project Name	Project Type	Project Status	Staff Name	Week Ending Date	Hrs	Base Cost Rate	TS Code	helper1
1	Alpha	Absence	On Going	Adam	01/11/2015	8	20	Core Time	160
1	Alpha	Absence	On Going	Barry	01/11/2015	8	25	Core Time	200
1	Alpha	Absence	On Going	Charlie	01/11/2015	8	30	Core Time	240
2	Beta	Holiday	On Going	Adam	01/09/2015	10	20	Core Time	200
2	Beta	Holiday	On Going	Adam	08/09/2015	20	20	Core Time	400
2	Beta	Holiday	On Going	Charlie	01/10/2015	5	30	Core Time	150
2	Beta	Holiday	On Going	Charlie	08/10/2015	0.5	30	Core Time	15
3	Gamma	Fee	Completed	Adam	25/10/2015	40	20	Core Time	800
3	Gamma	Fee	Completed	Adam	01/11/2015	30	20	Core Time	600
3	Gamma	Fee	Completed	Adam	08/11/2015	20	20	Core Time	400
3	Gamma	Fee	Completed	Adam	15/11/2015	25	20	Core Time	500
3	Gamma	Fee	Completed	Adam	22/11/2015	40	20	Core Time	800
3	Gamma	Fee	Completed	Adam	29/11/2015	17.5	20	Core Time	350
3	Gamma	Fee	Completed	Adam	06/12/2015	23	20	Core Time	460
3	Gamma	Fee	Completed	Adam	13/12/2015	40	20	Core Time	800
3	Gamma	Fee	Completed	Adam	20/12/2015	35	20	Core Time	700
4	Delta	Fee	On Going	David	25/10/2015	40	30	Core Time	1200
4	Delta	Fee	On Going	David	01/11/2015	10	40	OT 1	400
4	Delta	Fee	On Going	David	08/11/2015	10	40	OT 1	400
4	Delta	Fee	On Going	David	29/11/2015	5	55	OT 2	275
4	Delta	Fee	On Going	David	06/12/2015	5	55	OT 2	275
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
				01/09/2015	01/10/2015	01/11/2015	01/12/2015
				30/09/2015	31/10/2015	30/11/2015	31/12/2015
Project Number	Project Name	Project Type	Project Status	September	October	November	December	Total Project Cost
1	Alpha	Absence	On Going	0	0	600	0	600
2	Beta	Holiday	On Going	600	165	0	0	765
3	Gamma	Fee	Completed	0	800	2650	1960	5410
4	Delta	Fee	On Going	0	1200	1075	275	2550
tot monthly costs				600	2165	4325	2235	9325
	much easier with the helper column
	you need to think about using week ending dates in the main table and calendar months in the summary table
	eg week ending 1/11/15 has 6 days in october
	alpha november 600 formula is
	=SUMPRODUCT(($B$2:$B$22=$B38)*($J$2:$J$22)*($F$2:$F$22>G$34-1)*($F$2:$F$22<G$35+1))

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[MickG]

Try this:-
Results sheet2

[COLOR=Navy]Sub[/COLOR] MG03Nov35
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, n [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Mth [COLOR=Navy]As[/COLOR] [COLOR=Navy]Integer,[/COLOR] Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Q [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Dim[/COLOR] Col [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] K [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR=Navy]With[/COLOR] CreateObject("System.Collections.ArrayList")
        [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng.Offset(, 4)
            [COLOR=Navy]If[/COLOR] Not .Contains(MonthName(Month(Dn.Value))) [COLOR=Navy]Then[/COLOR] .Add MonthName(Month(Dn.Value))
        [COLOR=Navy]Next[/COLOR]
            .Sort: .Reverse
            ReDim ray(1 To Rng.Count, 1 To 5 + .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"
        [COLOR=Navy]For[/COLOR] n = 5 To 4 + .Count
            ray(1, n) = .toarray()(n - .Count - 1)
        [COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]End[/COLOR] With

n = 1
[COLOR=Navy]With[/COLOR] CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
        [COLOR=Navy]For[/COLOR] Ac = 5 To UBound(ray, 2) - 1
            [COLOR=Navy]If[/COLOR] Month("1-" & ray(1, Ac) & "-2000") = Month(Dn.Offset(, 4).Value) [COLOR=Navy]Then[/COLOR] Col = Ac
        [COLOR=Navy]Next[/COLOR] Ac
        [COLOR=Navy]If[/COLOR] Not .Exists(Dn.Value) [COLOR=Navy]Then[/COLOR]
            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
            .Add Dn.Value, Array(n, ray(n, Col))
        [COLOR=Navy]Else[/COLOR]
            Q = .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)
            .Item(Dn.Value) = Q
        [COLOR=Navy]End[/COLOR] If
    [COLOR=Navy]Next[/COLOR]


n = n + 1
ray(n, 1) = "Total Monthly Costs"
[COLOR=Navy]For[/COLOR] Ac = 5 To UBound(ray, 2)
    [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] .keys
        ray(n, Ac) = ray(n, Ac) + ray(.Item(K)(0), Ac)
    [COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]End[/COLOR] With


[COLOR=Navy]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(n, UBound(ray, 2))
    .NumberFormat = "#,##0"
    .Value = ray
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[MickG]

NB:- The previous code would not Resize for New Months.
Try this:-

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


[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, 4)
    [COLOR="Navy"]If[/COLOR] Not Dic.exists(Month(Dn.Value)) [COLOR="Navy"]Then[/COLOR]
        Dic.Add Month(Dn.Value), MonthName(Month(Dn.Value))
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Application
    ReDim mray(1 To Dic.Count)
        [COLOR="Navy"]For[/COLOR] n = .Min(Dic.keys) To .Max(Dic.keys)
            [COLOR="Navy"]If[/COLOR] Dic.exists(n) [COLOR="Navy"]Then[/COLOR]
                c = c + 1
                mray(c) = Dic(n)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] With
ReDim ray(1 To Rng.Count, 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"
        
        [COLOR="Navy"]For[/COLOR] n = 5 To 5 + Dic.Count - 1
            ray(1, n) = mray(n - 4)
        [COLOR="Navy"]Next[/COLOR] n
 Dic.RemoveAll
 n = 1
 [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]For[/COLOR] Ac = 5 To UBound(ray, 2) - 1
            [COLOR="Navy"]If[/COLOR] Month("1-" & ray(1, Ac) & "-2000") = Month(Dn.Offset(, 4).Value) [COLOR="Navy"]Then[/COLOR] Col = Ac
        [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
            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))
        [COLOR="Navy"]Else[/COLOR]
            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
        [COLOR="Navy"]End[/COLOR] If
    [COLOR="Navy"]Next[/COLOR]


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




[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(n, UBound(ray, 2))
    .NumberFormat = "#,##0"
    .Value = ray
    .Columns.AutoFit
    .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With


[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[mileijit]

Hi Mick,

Thank you for the VBA code.

I've tried to run it but it comes up with the following Run-time error '9': Subscript out of range

ray(n, 1) = "Total Monthly Costs"​

How do I rectify this?

Thanks in advance

 

[MickG]

First try altering line below:-

ReDim ray(1 To [B][COLOR=#ff0000]Rng.Count+1[/COLOR][/B], 1 To 5 + Dic.Count)

If that does not work, See example below and compare !!!
https://app.box.com/s/df08nnw7xoqdpm5quafvosc830blln0c

If that does not work you will need to send an example File (That also fails) Using "Box.com" or similar.