#  Other Applications & Softwares  > Access Tables & Databases >  >  Sum all amounts, for same code, for same unique id - Extract to just 1 line

## tyrese214

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....

----------


## 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)*

----------


## pike

tyrese214

This is not a "do it for me" forum. 
All post are with-in the forum; not to email addresses

_Your post does not comply with Rule 1 of our Forum_ RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title. 
*To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.*

*PLEASE PM WHEN YOU HAVE DONE THIS AND I WILL DELETE THIS POST*

----------


## tyrese214

> 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...

----------


## royUK

_Your post does not comply with Rule 7 of our Forum_ RULES. Please do not ignore Moderators' or Administrators' requests - *note that this includes requests by senior members as well*, if you are unclear about their request or instruction then send a private message to them asking for help. *Do not post a reply to a thread where a moderator has requested an action that has not been complied with e.g Title change or Code tags...etc*

----------

