Problem in defining a formula

[okicizepa]

Hi to all members of excelforum!
I have one big problem, there is one BIG excel file and I have to sum columns, I'll explain by example:
let's say that you have 2 columns - A (with names) and B (with values):

A B
bob 100
bob 999
bob 44
john 122
john 66
mary 456
mary 7878
mary 212
mary 52323

How to sum all numbers in B with name in A? how to sum all numbers with the name john and then how to sum all numbers with mary in column A?

It would be perfect if I can receive result:
A B
bob 15151
john 626
mary 654552

Of course, my file is much bigger with 200 different names in column A, and each name hase at least 50 numbers to sum...is there any way to do this quickly?

[Marcol]

Try this macro if you are happy with VBa

[EDIT]
This will work for 2007 and above (see Post #4 for 2003 version)

Please Login or Register  to view this content.

Hope this helps

[pb71]

Try a pivot table

... if you are not

EDIT:
Please see attached.

[Marcol]

Sorry, just noticed you are using 2003

The code in Post #2 is for 2007 and above, it will fail on the sort method if used with 2003

This should work for 2003 and above

Please Login or Register  to view this content.

Hope this helps

[martindwilson]

extract a unique list of names with advanced filter to say to col g
then against that list in g in h put =SUMIF($A$1:$A$2000,G1,$B$1:$B$1000)
see here
http://www.contextures.com/xladvfilter01.html
scroll down to Filter Unique Records

[teylyn]

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