Use VBA to Add Values Based on Multiple Criterias

[sanj_edu]

Hello,
I need to extract a summary from a large data set (I have mentioned an example).

The summary should add up the hours based on location, date and service. Some of the services are to be combined, in the example, you will notice that the hours for Domestic RRD and CTL RRD are added together for the same location and date.

Any help would be appreciated!
Thank you!


[TABLE="width: 640"]
<tbody>[TR]
[TD]Location[/TD]
[TD]Date[/TD]
[TD]Service[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]TYC[/TD]
[TD]12/1/2017[/TD]
[TD]Domestic RRD [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]TYC[/TD]
[TD]12/1/2017[/TD]
[TD]INTL RRD[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]TYC[/TD]
[TD]12/2/2017[/TD]
[TD]Domestic RRD [/TD]
[TD]2.5[/TD]
[/TR]
[TR]
[TD]TYC[/TD]
[TD]12/2/2017[/TD]
[TD]CTL RRD[/TD]
[TD]0.75[/TD]
[/TR]
[TR]
[TD]CAS[/TD]
[TD]12/1/2017[/TD]
[TD]Domestic RRD [/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]CAS[/TD]
[TD]12/2/2017[/TD]
[TD]INTL RRD[/TD]
[TD]9.5[/TD]
[/TR]
[TR]
[TD]CAS[/TD]
[TD]12/2/2017[/TD]
[TD]Domestic RRD [/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]CAS[/TD]
[TD]12/2/2017[/TD]
[TD]CTL RRD[/TD]
[TD]1.25[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]



[TABLE="width: 640"]
<tbody>[TR]
[TD]Output:[/TD]
[TD="colspan: 3"]Should be on next sheet[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location[/TD]
[TD]Date[/TD]
[TD]Service[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]TYC[/TD]
[TD]12/1/2017[/TD]
[TD]Domestic [/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]TYC[/TD]
[TD]12/2/2017[/TD]
[TD]Domestic [/TD]
[TD]3.25[/TD]
[/TR]
[TR]
[TD]TYC[/TD]
[TD]12/1/2017[/TD]
[TD]INTL[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]CAS[/TD]
[TD]12/1/2017[/TD]
[TD]Domestic [/TD]
[TD]3.5[/TD]
[/TR]
[TR]
[TD]CAS[/TD]
[TD]12/2/2017[/TD]
[TD]INTL[/TD]
[TD]9.5[/TD]
[/TR]
[TR]
[TD]CAS[/TD]
[TD]12/2/2017[/TD]
[TD]Domestic [/TD]
[TD]2.25[/TD]
[/TR]
</tbody><colgroup><col><col><col><col></colgroup>[/TABLE]

 

[AFPathfinder]

I suppose the elegant solution would be to create a class with a property for each column that utilizes a 2D array to build the summary table.

You could copy the table to the summary location and just loop through to make the changes necessary to condense to a summary.

Another option might be to have the macro to generate the table between 2 given dates and just use a COUNTIFS function to sum the hours.

The last option I can think of would be to skip the macro altogether, use "=LEFT(C2,LEN(C2)-4)" to remove the "RRD"s, replace all "CTL" with "Domestic", and use a pivot table to summarize, then copy/paste.

 

[Joe4]

It appears that you have Cross-Posted this here: https://chandoo.org/forum/threads/u...ased-on-multiple-criterias.36725/#post-220461
and many other places (as denoted in that link).

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules). This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.