Linda has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Grouping of values matching criteria then summing corresponding
Hi there,
I have a spreadsheet with all my trading data on it. Basically there are three relevant columns I want to collate. Lets call them A (text string), B (text string), and C (value).
I'm trying to write a macro that will look at A & B and if they are the same then sum C, then copy B to new sheet ref and copy C's summed figure to new sheet ref both under new column titled after A's value.
I'm not sure how to ask VBA to group the same values as one entry into new sheet and sum it.
Able to help??
Kind regards
Linda
RE: Grouping of values matching criteria then summing correspond
Hi Linda
Thank you for the question.
I'm not totally sure what you mean by grouping.
Do you mean you want totals for every different value in B that has a matching value in A?
Could you upload a simple example showing what you want the VBA to do?
Regards
Laura GB
RE: Grouping of values matching criteria then summing correspond
Hi Laura,
Here is another way of explaining my query
Trying to write a code to sum up the values in a column providing the two previous column values match up.
EG
AG AAA 123
AG AAA 321
AG CCC 456
AU AAA 252
PT AAA 322
PT AAA 111
PT BBB 333
So I want to grp all the AG&AAA's together (=444) then AG&CCC etc etc)
And I want to paste AAA into a column in new sheet alongside the total sum (444) under a heading for AG.
Hope this helps, I'd really appreciate the advice.
Linda
RE: Grouping of values matching criteria then summing correspond
Hi Linda
I am assuming you have already tried a pivot table and that doesn't answer your need.
ANother solution is to combine using DSUM and and using an Analysis Table. You need to create a single case of a Dsum and then the values you are interested in and then use Table from the Data menu. I have created a small file to demonstrate it. Let me know an email address and I will send it to you.
Regards,
Laura GB
RE: Grouping of values matching criteria then summing correspond
Hi Laura,
Don't worry, I managed to get what I wanted out of pivot table, I didn't think it would format nicely but turned out ok. I think what I was after was an array, still not entirely sure how to do it but will work it out if I need to do it again!
thanks for help
Linda
