Merging/combining dup data in rows and Summing data in columns

[FMPOTTER]

I have a four column worksheet. Columns A and B have duplicate data while the data in Columns C and D is distinct. I want to consolidate all the rows where there is duplicate data in Columns A and/or B and SUM the totals in Columns C and D respectively. The below screenshot samples show the before and after desired results. ***I DO NOT WANT TO REMOVE DUPLICATE ROWS AS THIS ALSO REMOVES DATA IN COLUMNS C & D THAT NEEDS TO BE SUBTOTALED.

Example:

Can anyone help?

 

[alansidman]

Cannot manipulate data in a picture. Please use XL2BB to upload this data so that we can manipulate the data and supply you with a workable solution.

 

[hagia_sofia]

Something like:

=UNIQUE(A5:B21)

=SUMIFS(C5:C21,A5:A21,G5:G10,B5:B21,H5:H10)

=SUMIFS(D5:D21,A5:A21,G5:G10,B5:B21,H5:H10)

 

[FMPOTTER]

Something like:

=UNIQUE(A5:B21)

=SUMIFS(C5:C21,A5:A21,G5:G10,B5:B21,H5:H10)

=SUMIFS(D5:D21,A5:A21,G5:G10,B5:B21,H5:H10)

This is not a viable option because the examples I showed in the "After

Cannot manipulate data in a picture. Please use XL2BB to upload this data so that we can manipulate the data and supply you with a workable solution.

I cannot load the XL2BB add-in to XL on my machine. It tells me it cannot install in "Protected View" and I can't figure out how to turn that off.

 

[FMPOTTER]

Something like:

=UNIQUE(A5:B21)

=SUMIFS(C5:C21,A5:A21,G5:G10,B5:B21,H5:H10)

=SUMIFS(D5:D21,A5:A21,G5:G10,B5:B21,H5:H10)

These formulas can't work because you are referencing cell ranges in the "After" screenshot that is the desired result. That is the format I want the final data to look like. Let me further explain. First, my worksheet has 16,500 rows of data. Think of each row being its own unique transaction. So technically, while there are duplicate listings of the same vendor and vendor address in columns A and B, each row is in fact unique. I want to consolidate all the vendors and addresses into single unique rows but total all the transactions in columns C and D for each.

1. I want to merge or consolidate all rows of data in Column A and Column B, resulting in a single unique line item for each entry in each column.
2. At the same time, I want to SUM all the rows in Column C and Column D for each entry in Columns A and B.

 

[alansidman]

If you cannot make XL2BB work, then load your file to either Box.net or Dropbox.com so that we can work with real data instead of guessing.

 

[Fuji]

I cannot load the XL2BB add-in to XL on my machine. It tells me it cannot install in "Protected View" and I can't figure out how to turn that off.

1. Go to File > Options.
2. Select Trust Center > Trust Center Settings > Protected View.
3. Clear the box for Enable Protected View for files originating from the internet.