Consolidate data depending on date value

[pete2586]

Hi,

I've tried looking at similar posts on this website and using the solutions but haven't managed to get anything to work.

My problem seems quite simple; I have two columns of data, A and B, where column A contains dates and column B a list of values. Some dates in column A are duplicated with each having unique values in column B. I would like to combine all dates that are the same by summing the the related values in column B then deleting all additional rows, leaving one record for each date in column A and the summed values in column B.

There is also another issue. Not every date is listed in column A, currently only dates that have a corresponding value in column B are given. I need to insert all missing dates into column A and obviously leave the column B for these inserted dates blank.

I hope this is clear enough, I've found it quite hard to write down the problem. I hope you can help.

Thanks.

 

[Dan Rittgers]

1) You could create a Pivot Table and then value it and then range name (Dates) it with date in the left column.

2) Create a column with all the dates (Column C)
3) Column D =Vlookup(C1,Dates,2,false) copy down Column D
4) Value Column D

 

[pete2586]

Thanks for the quick response. It all works perfectly after briefly fighting my fear of pivot tables.

Thanks again.