Excel Macro for a very customized group by function [Closed]

Report
-
Hello,
I would like to generate a report like the attached with this mail. The requirement is
- In the Sample Data, Source tab contains data for project wise shipments for various geographies across certain dates
- I need to get the data squeezed into one report like the one described in Destination tab that depicts the Project Wise data for geographies, where the Start Date will be the minimum Start Date for that project, End Date will be the Maximum of all End Dates corresponding to that project, and the number of shipments will be the quarterly sum of shipments for that project that will fall under a particular quarter/date range in the source file.

I tried multiple options using Pivot and all but it seems that there is no easy way to derive this other than a macro. I'm not very much comfortable with macros, had this been into SQL I could have done this. Need some help how do I perform this quickly in excel.

Early response will surely help.

Regards
Indranil
    • Source Tab:**
    • Project Name Type Domain Geo Shipments Start Date End Date**


Project 1 SA Dom1 Europe 3 2016-01-01 2016-03-31
Project 1 SA Dom1 Europe 2 2016-01-01 2016-01-29
Project 1 SA Dom1 Europe 1 2016-01-01 2016-01-31
Project 1 SA Dom1 Europe 12 2016-01-30 2016-12-31
Project 1 SA Dom1 Europe 12 2016-02-22 2016-12-31
Project 3 MS Dom1 Europe 10 2016-04-01 2016-12-31
Project 3 MS Dom1 Europe 1 2016-02-01 2016-02-19
Project 2 MS Dom1 LATAM 19 2016-01-01 2016-03-31
Project 1 SA Dom1 Europe 11 2016-02-25 2016-12-31
Project 1 SA Dom1 Europe 0 2016-02-20 2016-02-29
Project 3 MS Dom1 Europe 54 2016-03-07 2016-12-31
Project 3 MS Dom1 Europe 7 2016-04-04 2016-06-30
Project 2 MS Dom1 LATAM 2
Project 1 SA Dom1 Europe 13 2016-07-01 2016-12-31
Project 1 SA Dom1 Europe 9 2016-01-30 2016-12-31
Project 2 MS Dom1 LATAM 23 2016-04-01 2016-06-30
Project 4 SA Dom1 NZ 21 2016-01-01 2016-03-31
Project 4 SA Dom1 NZ 3 2016-01-01 2016-03-31
Project 4 SA Dom1 NZ 3 2016-01-01 2016-03-31
Project 4 SA Dom1 NZ 1 2016-02-22 2016-03-31
Project 4 SA Dom1 NZ 21 2016-04-01 2016-06-30
Project 4 SA Dom1 NZ 7 2016-04-01 2016-06-30
    • Destination Tab:**
    • Domain Geo Project Name Type Start Date End Date Shipments Q1 Shipments Q2 Shipments Q3 Shipments Q4 Total**

Dom1 Europe Project 1 SA 2016-01-01 2016-12-31 50 0 13 0 63
Dom1 Europe Project 3 MS 2016-02-01 2016-12-31 55 17 0 0 72
Dom1 LATAM Project 2 MS 2016-01-01 2016-06-30 21 23 0 0 44
Dom1 NZ Project 4 SA 2016-01-01 2016-06-30 28 28 0 0 56

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!