Grouping of values matching criteria then summing corresponding

Resolved · High Priority · Version 2003

Thu 23 Oct 2008, 12:45 Delegate Linda said...

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

Thu 23 Oct 2008, 18:09 Trainer Laura said...

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

Mon 27 Oct 2008, 12:14 Delegate Linda said...

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

Mon 10 Nov 2008, 10:13 Edited on Mon 10 Nov 2008, 10:19 Trainer Laura said...

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

Mon 10 Nov 2008, 12:15 Delegate Linda said...

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

Training courses

	98.7 % recommend us
	More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

Excel tip:

Sorting List Subtotals

If you find that you would prefer to show the items in a subtotalled list in a different order, eg ascending rather than descending, you can sort your list. To sort a subtotalled list, hide the detail rows and then sort the subtotal rows. When you sort a subtotalled list, the hidden detail rows are automatically moved with the subtotal rows.
IMPORTANT: If you do not hide the details rows before sorting a subtotalled list, your subtotals will be removed and all of the rows in your list will be reordered.

View all Excel hints and tips