Sum all amounts, for same code, for same unique id - Extract to just 1 line

Hi All,

I have an urgent excel or access task that I cant figure out.

Right now I have about 600 000 records of data that I need to summarise.

I have included an attachement to a sample of about just 100 to demonstrate what I am trying to achieve.

Please download it

Right now this data is in excel and the file is really HUGE.

This data is on a transactional level so for every unique ID (RED column C) there are many instances of Codes (GREEN Column B) with corresponding amounts

I need to summarise this whole dataset so that I am Cumulating the Amount (YELLOW Column A), for every code, for every Unique ID.


So in the workbook rather than having multiple rows of Code 1, if there is the same Unique ID, then sum up all the Amounts in Just 1 Row.


In this case rows 2 to 14 will be summed up/cumulated into row 1 of a new dataset with: 28210.65 in the "amount" column, 1 in the "code" column and 5 in the "Unique ID" column.

Rows 15 and 16 will need to be summed up into row 2 of this new dataset with: 601.37 in the "amount" column, 7 in the "code" column and 5 in the "unique ID" column.

Rows 17 to 22 will need to be summed up into row 3 of this new dataset with: 2871.7 in the "amount" column, 9 in the "code" column and 5 in the "unique ID" column.

If the Unique ID is the same, then the State will be the same, and I would like this data in as well. So for rows 1,2 & 3, each row will have QLD in the state column in the new dataset.

This rule will then need to be applied to all rows. That is, sum all amounts where code is the same And Unique Id is the same.


This will then condense this massive dataset into a more manageable size.

I dont know what the best way to do this.

I am great with excel, but this maybe an ACCESS task. Please give me the steps I need to take to achieve my requirements above.

I am not an Access genious so please provide me what i need to do.

Thank you to everyone here and I hope I can get a solutions soon..

You may email me the final solution to: tyrese213@hotmail.com


Many thanks....

Originally Posted by JBeaucaire

You can highlight columns B:D and run an Advanced Filter to another location / unique records only to get a list of the unique rows. I did that into G1 and got the short list.

Then I used a SUMPRODUCT() formula to collect the data, in J2 then copied down:

=SUMPRODUCT(($B$2:$B$105=G2)*($C$2:$C$105=H2)*($D$2:$D$105=I2)*$A$2:$A$105)

Thank you very much for your time and expertise... it was exactly what i was after.. your a genious...