Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old July 3rd 09, 12:33 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 1
Default combine rows and sum data with the same id

Hi,
Here is an example of the data I have.
Account ID Amount
101567 $505.00
101567 $875.00
101567 $2,450.50
253570 $1,178.90
253570 $875.00
346677 $1,928.33
I have thousands of accounts with multiple amounts having the same ID and I
need to add these amounts and combine them so that there is only one row per
account ID. Please help...





  #2   Report Post  
Old July 3rd 09, 12:54 AM posted to microsoft.public.excel.misc
Max Max is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 9,221
Default combine rows and sum data with the same id

One good, quick way - Use a pivot. It'll deliver exactly what you seek, in a
matter of seconds, and despite the voluminous data

Create a pivot (click DataPivotTable), then in step 3 of the wizard, click
Layout, drag n drop "Account ID" into ROW area, "Amount" into DATA area
(it'll show as Sum of Amount). Click OK Finish. That's it. Hop over to the
pivot sheet for the amazing results delivered: a listing of all the unique
Account IDs in a column with their corresponding sums next to it

Wave your success here, click the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"tenny" wrote:
Here is an example of the data I have.
Account ID Amount
101567 $505.00
101567 $875.00
101567 $2,450.50
253570 $1,178.90
253570 $875.00
346677 $1,928.33
I have thousands of accounts with multiple amounts having the same ID and I
need to add these amounts and combine them so that there is only one row per
account ID. Please help...


  #3   Report Post  
Old July 3rd 09, 01:05 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2009
Posts: 1
Default combine rows and sum data with the same id


hi Tenny,

I suggest using a Pivot Table (PT) to summarise your data (this is just
of many possible solutions). Here is Jon Peltier's index page which has
links to a lot of useful PT reading:
'Using Pivot Tables and Pivot Charts in Microsoft Excel'
(http://peltiertech.com/Excel/Pivots/pivotstart.htm)
including this initial explanation of what Pivot tables do:
'Using Pivot Tables in Microsoft Excel'
(http://peltiertech.com/Excel/Pivots/pivottables.htm)

hth
Rob


--
broro183

Rob Brockett. Always learning & the best way to learn is to
experience...
------------------------------------------------------------------------
broro183's Profile: http://www.thecodecage.com/forumz/member.php?userid=333
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=112303

  #4   Report Post  
Old July 3rd 09, 05:54 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2009
Posts: 37
Default combine rows and sum data with the same id

Hi Tenny

I'd use Data Consolidation. If your data is arranged as you showed, then
just click in the cell where you want your data to start-I would place it on
another worksheet. Go to DataConsolidate and choose Sum in the "Function:"
box. Then click the minimize button in the "Reference:" area. Highlite the
account numbers and data in your spreadsheet, then click back into the
Consolidate box. Click the Add button. In the "Use labels in" box, click
Left column, then click OK. If done correctly, you should have 1 row for
each unique account # and the corresponding summed amounts on the new
worksheet.

Please let me know if it works...
--
OneFineDay


"tenny" wrote:

Hi,
Here is an example of the data I have.
Account ID Amount
101567 $505.00
101567 $875.00
101567 $2,450.50
253570 $1,178.90
253570 $875.00
346677 $1,928.33
I have thousands of accounts with multiple amounts having the same ID and I
need to add these amounts and combine them so that there is only one row per
account ID. Please help...






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 do I combine multiple rows with like data in Excel? [email protected]&G Excel Discussion (Misc queries) 2 May 22nd 07 06:47 PM
Combine Data from Multiple Rows MR Excel Discussion (Misc queries) 1 January 24th 07 07:44 PM
How do I combine data from separate Excel rows Pete New Users to Excel 2 October 19th 06 07:03 PM
Combine the data in 2 columns of 20 rows into one column of 40 row Tom Excel Discussion (Misc queries) 6 May 3rd 06 09:27 AM
Combine data rows in Pie Chart Jayntree Charts and Charting in Excel 1 March 16th 06 10:15 AM


All times are GMT +1. The time now is 02:28 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017