Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
dan dan is offline
external usenet poster
 
Posts: 866
Default 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
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 141
Default 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


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,836
Default 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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how can I convert data with fields in rows to fields as columns PiyushAg Excel Discussion (Misc queries) 3 July 2nd 07 05:46 AM
Trying to match certain fields from 1 workbook to another AngelaD Excel Discussion (Misc queries) 0 January 15th 07 08:02 PM
Duplicate fields does not match up! If statement Patsy Excel Worksheet Functions 0 November 11th 04 12:16 AM
Duplicate fields does not match up! If statement Patsy Excel Worksheet Functions 1 November 10th 04 10:26 PM
Duplicate fields does not match up! If statement Patsy Excel Worksheet Functions 0 November 3rd 04 03:53 PM


All times are GMT +1. The time now is 12:31 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"