Match Corresponding Rows and Add Fields Together
 

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

Match Corresponding Rows and Add Fields Together
 

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

Match Corresponding Rows and Add Fields Together
 

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