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!

Location	Date	Service	Hours
TYC	12/1/2017	Domestic RRD	2
TYC	12/1/2017	INTL RRD	7
TYC	12/2/2017	Domestic RRD	2.5
TYC	12/2/2017	CTL RRD	0.75
CAS	12/1/2017	Domestic RRD	3.5
CAS	12/2/2017	INTL RRD	9.5
CAS	12/2/2017	Domestic RRD	1
CAS	12/2/2017	CTL RRD	1.25

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

Output:	Should be on next sheet
Location	Date	Service	Hours
TYC	12/1/2017	Domestic	2
TYC	12/2/2017	Domestic	3.25
TYC	12/1/2017	INTL	7
CAS	12/1/2017	Domestic	3.5
CAS	12/2/2017	INTL	9.5
CAS	12/2/2017	Domestic	2.25

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

 

[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.