Need to consolidate and SUM multiple data sets

Hey guys, I'm new here and hope someone can help. I am working on a payout sheet with thousands of rows that need to be consolidated. Example attached. The end result needed is as follows:

Each individual (column B) on this sheet is assigned an owner ID (column A). There is also a Parcel ID (column C) for each unique property, and then an amount owed (a new row for each month) to that individual for that property. These amounts are added to the sheet every month, accumulated for years. I was hoping that there is a way for me to consolidate this data in a way so that there is only one row for every combination of column A and C, and also SUM the amounts for the combined rows. I know there has to be an easier way other than manually copying and consolidating all of this into a new sheet which is what I was asked to do. Please see the example attached, columns G though J is what the end goal needs to be.

G2=IFERROR(INDEX(A$2:A$1000,SMALL(IF(FREQUENCY(IF($A$2:$A$1000<>"",MATCH($A$2:$A$1000&$B$2:$B$1000&$C$2:$C$1000,$A$2:$A$17&$B$2:$B$1000&$C$2:$C$1000,0)),ROW($A$2:$A$1000)-ROW($A$2)+1),ROW($A$2:$A$17)-ROW($A$2)+1),ROWS($A$1:A1))),"")

For office 365 enter for other office control+shift+enter

copy across and down from g2 to i2


J2=SUMIFS($D$2:$D$1000,$A$2:$A$1000,$G2,$B$2:$B$1000,$H2,$C$2:$C$1000,$I2)

Copy down

with Power Query
(tailored to your example)

Please Login or Register  to view this content.

Yes, it even works for small subsets of data within the workbook. However, when I apply the formula to accommodate all 5,670 rows of data the numbers stop adding up. For example, my first 50 lines of data are all the same person. If I apply the formula to only those rows, no issue. I can even apply it to the next 100 or so rows without issue. It is only when applying it to the whole that the numbers break. Thoughts?

Sorry I disappeared for a while there guys. I got pulled onto another higher priority project and am just now coming back to this.

Sandy,
I am currently trying to figure out how to apply your power query to the actual workbook. I have never used it before, so this is a good opportunity to learn, as it does not seem to be as easy as copy/paste. If I cannot get this work, I will try to find a better example to post without disclosing any company info.

Thank you all for your help and patience.

I just figured that out and it works perfectly, Thank you. Now I have a couple of questions if you don't mind. In the original workbook there are more columns, that aren't relevant to what needs to be reported, but would be easier overall if kept in. Can Power Query still perform this task, and copy that other data without factoring it into the consolidation process? Alternatively, does it pull from the entire table or can certain columns be specified? Also, can the table from the Power Query be populated on another sheet within the workbook? Or can it only do so within the same sheet?

TMS,

Not discrediting your first formula. It was working great for smaller subsets of data, I just could not figure out why the numbers were getting mixed when applied to the whole sheet. My first thought was that maybe it was due to the existence of both positive and negative dollar amounts, but when separating a small portion of those out and applying the same formula it would still function perfectly. However, I could only get the other formulas provided by you and Caracalla to work on the example sheet. Couldn't figure out why. I will most likely toy around with them all after the fact to satisfy my own curiosity.

I have successfully accomplished the task at hand. Thank all of you for your input. You are all truly Excel Wizards.

You are welcome and thanks

Is it possible to get the number of package IDs that were added together?
please in column K

George,

If that question is directed at me then I'm sorry, I don't fully understand what you are asking for.