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.
8 Comments
Nomecks@lemmy.ca · 1 pts · 242d
Use VBA to loop through the project IDs, sum the hours and delete the extra rows
Millennial_Falcon@lemmy.world · 1 pts · 238d
Is there any tutorial you can link me to better understand this process? Unsure what VBA is.
Nomecks@lemmy.ca · 1 pts · 238d
Most tutorials on Youtube are good. Essentially it's the programming behind macros. Really powerful for automating spreadsheet tasks
Millennial_Falcon@lemmy.world · 1 pts · 242d
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
Matth@sh.itjust.works · 1 pts · 242d
Use either a pivot table or power query to group rows.
Eventually if you want to streamline it more you could :
roysta@lemmy.world · 1 pts · 242d
https://www.exceldemy.com/excel-sum-by-group/
Millennial_Falcon@lemmy.world · 1 pts · 242d
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.
Matth78@piefed.social · 1 pts · 236d
Baffling7900@lemmy.world · 1 pts · 211d
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.