How to aggregate a large amount of data into a single line value
Hey there,
I have a sample of data below in which there are multiple rows with different hours that all pertain to the same project id and task name.
I am trying to find a way to consolidate the duplicates into a single total for each.
For example, Altos Ohio I want to keep a single line for the Project ID, Task name (May) but have a single hour value of 1.5, rather than the 3 separate entries rows of 0.7, 0.5 and 0.3 as seen in the image.
The reason is due to my import tool, it will import them as 3 separate entries, but the goal end result would be to have the total hours as a single entry so I need to clean up this data before import.
Check out PivotTables: https://www.youtube.com/watch?v=aofsdpjvK7w (Chandoo, 10:15)
It's a very basic intro video; and yours is kind of a straight forward case. After handling the immediate task with PivotTables, I'd suggest then to slowly dive deeper and move towards Power Query.
Use VBA to loop through the project IDs, sum the hours and delete the extra rows
Is there any tutorial you can link me to better understand this process? Unsure what VBA is.
Most tutorials on Youtube are good. Essentially it's the programming behind macros. Really powerful for automating spreadsheet tasks
Essentially, i want to merge all duplicates of project id, task name, task start date, task end date into a single row with the hours being a total of each duplicate row
Use either a pivot table or power query to group rows.
Eventually if you want to streamline it more you could :
https://www.exceldemy.com/excel-sum-by-group/
None of these were really helpful or achieved what i needed. Articles weren't clear on how to apply these functions to datasets that weren't perfect matches to the examples shown.