Sort and Add Columns

[Guest]

Here is my problem. I have a file that is a combination of data from other files. I have that set up just fine. I have names in the first column and data in the second through the eight. What I need to do is find a way to sort by the name as there will be multipe line with the same name in the first column and then add all the data in the other columns together. It looks something like thi

John 3 4 5
John 2 8 5
Mary 3 5 8
Paul 4 6 7
Mary 2 7 9

It needs to condense down so that I would just have three names ie. John, Mary, and Paul and the data in the other columns would be added up for that name ie.

John 5 12 10 1
Mary 5 12 17
Paul 4 6 7

This data is about 1500 rows long so doing it by hand will be a difficult task. If anybody has any ideas it would be geatly appreciated. I know only the basics of recording macros, and I have no idea how else to do it

Thanks

Vinc

 

[Kieran]

Vince,

This is what pivot tables are good at.
If you have not used them before, have a go with the online help. If i
still seems strange (it was to me the first few times), post back an
give more specifics.

Hint - each data column will need a label

 

[Guest]

I had looked at pivot table but I need to be able to manipulate the data that is added together and this data gets exported to another sheet after I get it sorted and do calculations with it,so I don't see how a pivot table can do the job. Although I will take another look at using tables, I still then a macro is really the only way for me to go

Vince

 

[Paul Robinson]

Hi Vince
No need for macros:
1. Select the column of names. Go to Data, Filter, Advanced Filter.
Check "copy to another location", leave the Criteria Range box blank,
Location should be a blank region on the same worksheet - say below
your table and click on one cell; finally, check the unique records
only box.
You now have each name once.
2. With the original column of names still selected, go to Insert,
Names, Define... and give the selection a name e.g. MyNames
3. Suppose your list of unique names starts at A10 and that your data
set is in A1:E5.
In cell A11 type the formula
=sum(if(MyNames = A10,offset(MyNames,0,1)))
and enter this as an array formula by pressing Ctrl+Shift+Enter
instead of enter.
Fill this formula down in the rest of this COLUMN
4. For the other 3 columns of numbers change the column parameter in
offset to 2, 3 and 4 respectively.

regards
Paul

 

[Guest]

Ok, but can I set all that up in a workbook to do automatically, that I guess is kind of an important part that I left out - I have to be able to set this up so that people who don't know excel can do this. That is the main reason I thought of using a macro because it can automate the process.

Vince

 

[Paul Robinson]

Hi Vince
Does your data have column headers? (name etc)
Do you want your sub totalled results on the same sheet, a different
but existing sheet or a new sheet, each time you run the macro?
Do you expect a user to run the macro more than once? If so, do you
want to replace the result of the first run or create a new result?

regards
Paul