Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort and Add Columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort and Add Columns
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 -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort and Add Columns
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort and Add Columns
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 "Vince Henschel" wrote in message ... 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 this John 3 4 5 6 John 2 8 5 9 Mary 3 5 8 1 Paul 4 6 7 3 Mary 2 7 9 2 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 15 Mary 5 12 17 3 Paul 4 6 7 3 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, Vince |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort and Add Columns
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sort and Add Columns
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 "Vince Henschel" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort by color: Is there an easy way to sort columns or rows in EX | Excel Worksheet Functions | |||
sort by two columns ends up with wrong sort in 2nd col | Excel Discussion (Misc queries) | |||
Sort command does not sort some columns? | New Users to Excel | |||
How do I sort the data in 8 columns by two of the columns? | Excel Worksheet Functions | |||
data sort is not including all columns in sort | Excel Discussion (Misc queries) |