Find, Sum, and Remove Duplicates in 3 different subsets of data in a spreadsheet

Question

I have a spreadsheet that contains truck load data. The spreadsheet contains each portion of the delivery cycle; Month-Year, Supplier(VDH_Columns) to Vendor Pickup(VPK_Columns) to Receiver(CUST_Columns) and Tons delivered(Tons). If the Vendor Pickup Columns are populated, the cycle goes VPK to CUST. If the Vendor Pickup Columns are not populated, the cycle goes VDH to CUST.

I want to maintain the spreadsheet exactly how it is and not make a CUST sheet, VPK Sheet, and VDH Sheet. The ultimate goal is to have 1 Month_Year for each Receiver(CUST) and sum either the VDH or the VPK. Below is a piece of the spreadsheet(What it looks like now) and I manually created what I want it to look like.Spreadsheet Example I tried a pivot table with a Month_Year filter but the problem is that I can't see once I try to add all the other columns it becomes so busy that I end up quitting. I've thought about Consolidate but I have no experience with that. I'm assuming VBA code would be able to accomplish it but I have no experience there either. Thanks for any help

Answer 1

A Pivot Table is the way to do this. You need to drag all of your columns to the "Rows" field, and then drag the Tons column to Values. It should automatically use a SUM summarization:

Then, you would select a Tabular layout from the Design tab.

This will give you the desired output:

You can get rid of the (blank) values by using Conditional Formatting on the Pivot Table. Use custom number format ;;;