Hello,
I have searched this site for a while and still can't find the solution to my problem. I was hoping someone here could point me to the right tool/formula. I am trying to aggregate data with breaks when product changes and adds new row. I have data as below:
| Available Data |
| Product ID | Product Name | Quantity | Start Date | End Date |
| 80052390 | Laptop | 2 | 3/29/2017 | 4/22/2017 |
| 80052390 | Laptop | 4 | 5/16/2017 | 7/12/2017 |
| 20460200 | Iphone | 3 | 4/23/2017 | 7/25/2017 |
| 20460200 | Iphone | 3 | 8/15/2017 | 9/15/2017 |
| 80052390 | Laptop | 4 | 8/12/2017 | 9/15/2017 |
| 31247004 | Charger | 5 | 3/29/2017 | 7/25/2017 |
| 20460200 | Iphone | 3 | 10/25/2017 | 12/18/2017 |
I am trying to aggregate data as below:
| Desired Output |
| Product ID | Product Name | Quantity | Min of Start Date | Max. of End Date |
| 80052390 | Laptop | 6 | 3/29/2017 | 7/12/2017 |
| 20460200 | Iphone | 6 | 4/23/2017 | 9/15/2017 |
| 80052390 | Laptop | 4 | 8/12/2017 | 9/15/2017 |
| 31247004 | Charger | 5 | 3/29/2017 | 7/25/2017 |
| 20460200 | Iphone | 3 | 10/25/2017 | 12/18/2017 |
Here I want to sum up Quantity and have minimum of start date and maximum of end date. I tried summarize tool but it aggregates all the records and doesn't break if the product changes. I would really appreciate someone's push in the right direction. Please let me know if I need to provide any more information.
Thanks a lot 🙂
