I've got a spreadsheet full of invoice line details. The columns are things like Item #, Item Name, Invoice Date, Qty sold, Dollar amount, and Customer.
I'm trying to consolidate it so it still shows *all* columns, but all sales for the same items to the same customer during the same month should be combined.
So for example, if there are 3 separate rows for the same item sold to the same customer during the same month:
Item # ABC, Item Name: Widget, Invoice date: 8/1/15, Qty sold: 30, dollar amt $30, Customer Acme
Item # ABC, Item Name: Widget, Invoice date: 8/15/15, Qty sold: 20, dollar amt $20, Customer Acme
Item # ABC, Item Name: Widget, Invoice date: 8/16/15, Qty sold: 50, dollar amt $50, Customer Acme
I'd want that particular instance "consolidated" to just one line:
Item # ABC, Item Name: Widget, Invoice month: Aug, Qty sold: 100 (total), dollar amt $100 (total), Customer Acme
I've tried messing around with Pivot Tables and Consolidate, but can't figure out how to get from A to B. Any suggestions?
(I'm using Excel 2011 on a Mac, OS: 10.11)
Bookmarks