Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
I have a worksheet which has over 1000 rows of data. Some of the rows have
corresponding data in the first 5 columns and then new data in the following 10. I need to filter the document so that a match is performed on the first few columns then the data in the remaining columns is added together. I know I can add a filter and then look at each case but I would need to do this nearly 300 time to have the data in the end of the columns consolidated. I have also looked at doing this through a pivot table but I have multiple columns that need additing together. Thanks Dan |
#2
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
Hi,
Hard to say without seeing a sample of your data... I can't quite picture it. Staying with the pivot table idea though, you can create a calculated field that may do what you want. When you are at the pivot table stage, on the PivotTable toolbar, click 'Pivot Table' - 'Formulas' - 'Calculated Field'. Call it whatever you want in the Name field, then you can just double click the fields in the listbox and that will add them to your formula. For example, you might have = "Col D" + "Col E" + "Col F" or something. Then just hit "OK" and it will add it to the data area. Not sure if this is what you are looking for, but just an idea. Cheers, Ivan. On Apr 2, 9:26*pm, Dan wrote: I have a worksheet which has over 1000 rows of data. *Some of the rows have corresponding data in the first 5 columns and then new data in the following 10. *I need to filter the document so that a match is performed on the first few columns then the data in the remaining columns is added together. *I know I can add a filter and then look at each case but I would need to do this nearly 300 time to have the data in the end of the columns consolidated. *I have also looked at doing this through a pivot table but I have multiple columns that need additing together. Thanks Dan |
#3
![]()
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
![]()
A Pivot Table should work fine; just include the critical elements in the row
region, then sum the elements you need to sum in the data region. I can't see your data, but I suspect it would work. Try it and see if it works. Regards, Ryan--- -- RyGuy "Ivyleaf" wrote: Hi, Hard to say without seeing a sample of your data... I can't quite picture it. Staying with the pivot table idea though, you can create a calculated field that may do what you want. When you are at the pivot table stage, on the PivotTable toolbar, click 'Pivot Table' - 'Formulas' - 'Calculated Field'. Call it whatever you want in the Name field, then you can just double click the fields in the listbox and that will add them to your formula. For example, you might have = "Col D" + "Col E" + "Col F" or something. Then just hit "OK" and it will add it to the data area. Not sure if this is what you are looking for, but just an idea. Cheers, Ivan. On Apr 2, 9:26 pm, Dan wrote: I have a worksheet which has over 1000 rows of data. Some of the rows have corresponding data in the first 5 columns and then new data in the following 10. I need to filter the document so that a match is performed on the first few columns then the data in the remaining columns is added together. I know I can add a filter and then look at each case but I would need to do this nearly 300 time to have the data in the end of the columns consolidated. I have also looked at doing this through a pivot table but I have multiple columns that need additing together. Thanks Dan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
how can I convert data with fields in rows to fields as columns | Excel Discussion (Misc queries) | |||
Trying to match certain fields from 1 workbook to another | Excel Discussion (Misc queries) | |||
Duplicate fields does not match up! If statement | Excel Worksheet Functions | |||
Duplicate fields does not match up! If statement | Excel Worksheet Functions | |||
Duplicate fields does not match up! If statement | Excel Worksheet Functions |