Summarizing data from long list to Master spreadsheet

M

mileijit

New Member
Joined
Oct 31, 2015
Messages
10
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!!!
 
Last edited:

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
SteveO59L said:
have you considered a pivot table?
Click to expand...

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 :(
 
Last edited:
Upvote 0
[TABLE="width: 985"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><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]
[TD]helper1[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Alpha[/TD]
[TD]Absence[/TD]
[TD]On Going[/TD]
[TD]Adam[/TD]
[TD="align: right"]01/11/2015[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]160[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Alpha[/TD]
[TD]Absence[/TD]
[TD]On Going[/TD]
[TD]Barry[/TD]
[TD="align: right"]01/11/2015[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]25[/TD]
[TD]Core Time[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Alpha[/TD]
[TD]Absence[/TD]
[TD]On Going[/TD]
[TD]Charlie[/TD]
[TD="align: right"]01/11/2015[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]30[/TD]
[TD]Core Time[/TD]
[TD="align: right"]240[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Beta[/TD]
[TD]Holiday[/TD]
[TD]On Going[/TD]
[TD]Adam[/TD]
[TD="align: right"]01/09/2015[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]200[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Beta[/TD]
[TD]Holiday[/TD]
[TD]On Going[/TD]
[TD]Adam[/TD]
[TD="align: right"]08/09/2015[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Beta[/TD]
[TD]Holiday[/TD]
[TD]On Going[/TD]
[TD]Charlie[/TD]
[TD="align: right"]01/10/2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]30[/TD]
[TD]Core Time[/TD]
[TD="align: right"]150[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Beta[/TD]
[TD]Holiday[/TD]
[TD]On Going[/TD]
[TD]Charlie[/TD]
[TD="align: right"]08/10/2015[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]30[/TD]
[TD]Core Time[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD="align: right"]25/10/2015[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD="align: right"]01/11/2015[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]600[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD="align: right"]08/11/2015[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD="align: right"]15/11/2015[/TD]
[TD="align: right"]25[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]500[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD="align: right"]22/11/2015[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD="align: right"]29/11/2015[/TD]
[TD="align: right"]17.5[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]350[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD="align: right"]06/12/2015[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]460[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD="align: right"]13/12/2015[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]800[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]Adam[/TD]
[TD="align: right"]20/12/2015[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]20[/TD]
[TD]Core Time[/TD]
[TD="align: right"]700[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]David[/TD]
[TD="align: right"]25/10/2015[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]30[/TD]
[TD]Core Time[/TD]
[TD="align: right"]1200[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]David[/TD]
[TD="align: right"]01/11/2015[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[TD]OT 1[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]David[/TD]
[TD="align: right"]08/11/2015[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]40[/TD]
[TD]OT 1[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]David[/TD]
[TD="align: right"]29/11/2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]55[/TD]
[TD]OT 2[/TD]
[TD="align: right"]275[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]David[/TD]
[TD="align: right"]06/12/2015[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]55[/TD]
[TD]OT 2[/TD]
[TD="align: right"]275[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 5"]What I'd like is to have a Master sheet automatically capture the following data:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[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="colspan: 2"]Total Project Cost[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Alpha[/TD]
[TD]Absence[/TD]
[TD]On Going[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD="align: right"]600[/TD]
[TD]-[/TD]
[TD="align: right"]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Beta[/TD]
[TD]Holiday[/TD]
[TD]On Going[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]165[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD="align: right"]765[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD]-[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]2,650[/TD]
[TD="align: right"]1,960[/TD]
[TD="align: right"]5,410[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD]-[/TD]
[TD="align: right"]1,200[/TD]
[TD="align: right"]1,075[/TD]
[TD]-[/TD]
[TD="align: right"]2,550[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Total Monthly Costs[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]2,165[/TD]
[TD="align: right"]4,325[/TD]
[TD="align: right"]2,235[/TD]
[TD="align: right"]9,325[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]01/09/2015[/TD]
[TD="align: right"]01/10/2015[/TD]
[TD="align: right"]01/11/2015[/TD]
[TD="align: right"]01/12/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]30/09/2015[/TD]
[TD="align: right"]31/10/2015[/TD]
[TD="align: right"]30/11/2015[/TD]
[TD="align: right"]31/12/2015[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[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="colspan: 2"]Total Project Cost[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Alpha[/TD]
[TD]Absence[/TD]
[TD]On Going[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Beta[/TD]
[TD]Holiday[/TD]
[TD]On Going[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]165[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]765[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Gamma[/TD]
[TD]Fee[/TD]
[TD]Completed[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]800[/TD]
[TD="align: right"]2650[/TD]
[TD="align: right"]1960[/TD]
[TD="align: right"]5410[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Delta[/TD]
[TD]Fee[/TD]
[TD]On Going[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1200[/TD]
[TD="align: right"]1075[/TD]
[TD="align: right"]275[/TD]
[TD="align: right"]2550[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]tot monthly costs[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]2165[/TD]
[TD="align: right"]4325[/TD]
[TD="align: right"]2235[/TD]
[TD="align: right"]9325[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]much easier with the helper column[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 7"]you need to think about using week ending dates in the main table and calendar months in the summary table[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 3"]eg week ending 1/11/15 has 6 days in october[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]alpha november 600 formula is[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 6"]=SUMPRODUCT(($B$2:$B$22=$B38)*($J$2:$J$22)*($F$2:$F$22>G$34-1)*($F$2:$F$22<G$35+1))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Try this:-
Results sheet2
Code: 
[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
 
Upvote 0
NB:- The previous code would not Resize for New Months.
Try this:-
Code: 
[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
 
Upvote 0
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 :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question Question
VBA code clean up
Replies
9
Views
482
mumps
mumps
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
286
excelnewbie999
E
D
  • Solved
Filter large amount of data by multiple constraints
Replies
7
Views
485
Peter_SSs
Peter_SSs
T
  • Question Question
Flattening a hierarchy from 5 columns into a unique one
Replies
1
Views
725
Kerryx
K
J
  • Solved
Sorting a Lot of data into multiple sheets (up to 200k lines)
Replies
4
Views
310
JMWh1t3
J

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top