Sum of common rows

I am trying to find an automated way of summing common row 
data. For example:

ORIGINAL DATA
Row1   123   John Smith
Row2   456   John Smith
Row3   678   John Smith
Row4   324   Dave Jones
Row5   678   Dave Jones

DESIRED DATA
Row1   1257  John Smith
Row2   1002  Dave Jones

I have several thousand rows and don't want to write 
something that is specific to the data that appears in the 
second column.

Any ideas?
0
9/4/2003 3:00:07 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
568 Views

Similar Articles

[PageSpeed] 25

Try this

Row 1(col A and B) must have a header like Number and Name
It will make a Unique list in Col C and the sum in Col D

Sub test()
    Dim LastRow As Long
    With Sheets("sheet1")
        .Range("b:b").AdvancedFilter _
        Action:=xlFilterCopy, CopyToRange:=.Range("c1"), _
                        CriteriaRange:="", Unique:=True

        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        For Each cell In .Range("c2:c" & LastRow)
            If cell.Value = "" Then Exit Sub
            cell.Offset(0, 1) = _
            Application.WorksheetFunction.SumIf( _
            .Range("b:b"), "=" & cell.Value, .Range("A:A"))
        Next
    End With
End Sub


-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl



"Holly Bodger" <holly.bodger@cma.ca> wrote in message news:1dee01c372f5$3aab6190$a601280a@phx.gbl...
> I am trying to find an automated way of summing common row
> data. For example:
>
> ORIGINAL DATA
> Row1   123   John Smith
> Row2   456   John Smith
> Row3   678   John Smith
> Row4   324   Dave Jones
> Row5   678   Dave Jones
>
> DESIRED DATA
> Row1   1257  John Smith
> Row2   1002  Dave Jones
>
> I have several thousand rows and don't want to write
> something that is specific to the data that appears in the
> second column.
>
> Any ideas?


0
rondebruin (3789)
9/4/2003 3:41:31 PM
Dave posted this links today

Debra Dalgleish's pictures at Jon Peltier's site:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
And Debra's own site:
http://www.contextures.com/xlPivot01.html

John Walkenbach also has some at:
http://j-walk.com/ss/excel/files/general.htm
(look for Tony Gwynn's Hit Database)

Chip Pearson keeps Harald Staff's notes at:
http://www.cpearson.com/excel/pivots.htm

MS has some at (xl2000 and xl2002):
http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx



-- 
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl



"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message news:O0OCpzvcDHA.2384@TK2MSFTNGP09.phx.gbl...
> Pivot Table / Pivot Table / Pivot Table!!!!!!!!!!!!!!!!!!!!!!!!
>
> For an excellent intro to something that will make you go WOW!!!!!, wing your way on over to Debra
> Dalgleish's intro at:-
>
> http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm
>
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                    Sys Spec - Win XP Pro /  XL2K & XLXP
>
> ----------------------------------------------------------------------------
>   Attitude - A little thing that makes a BIG difference
> ----------------------------------------------------------------------------
>
>
>
> "Holly Bodger" <holly.bodger@cma.ca> wrote in message
> news:1dee01c372f5$3aab6190$a601280a@phx.gbl...
> > I am trying to find an automated way of summing common row
> > data. For example:
> >
> > ORIGINAL DATA
> > Row1   123   John Smith
> > Row2   456   John Smith
> > Row3   678   John Smith
> > Row4   324   Dave Jones
> > Row5   678   Dave Jones
> >
> > DESIRED DATA
> > Row1   1257  John Smith
> > Row2   1002  Dave Jones
> >
> > I have several thousand rows and don't want to write
> > something that is specific to the data that appears in the
> > second column.
> >
> > Any ideas?
>
>


0
rondebruin (3789)
9/4/2003 4:01:21 PM
The other option of course, is to simply sort on that second column, select all your data and do
Data / Subtotals and then choose which fields you want to sum/count etc

--
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:O0OCpzvcDHA.2384@TK2MSFTNGP09.phx.gbl...
> Pivot Table / Pivot Table / Pivot Table!!!!!!!!!!!!!!!!!!!!!!!!
>
> For an excellent intro to something that will make you go WOW!!!!!, wing your way on over to
Debra
> Dalgleish's intro at:-
>
> http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm
>
> --
> Regards
>            Ken.......................    Microsoft MVP - Excel
>                    Sys Spec - Win XP Pro /  XL2K & XLXP
>
> ----------------------------------------------------------------------------
>   Attitude - A little thing that makes a BIG difference
> ----------------------------------------------------------------------------
>
>
>
> "Holly Bodger" <holly.bodger@cma.ca> wrote in message
> news:1dee01c372f5$3aab6190$a601280a@phx.gbl...
> > I am trying to find an automated way of summing common row
> > data. For example:
> >
> > ORIGINAL DATA
> > Row1   123   John Smith
> > Row2   456   John Smith
> > Row3   678   John Smith
> > Row4   324   Dave Jones
> > Row5   678   Dave Jones
> >
> > DESIRED DATA
> > Row1   1257  John Smith
> > Row2   1002  Dave Jones
> >
> > I have several thousand rows and don't want to write
> > something that is specific to the data that appears in the
> > second column.
> >
> > Any ideas?
>
>


0
ken.wright (2489)
9/4/2003 4:04:19 PM
Thanks Ron - appreciate it.

--
Regards
           Ken.......................    Microsoft MVP - Excel
                   Sys Spec - Win XP Pro /  XL2K & XLXP

----------------------------------------------------------------------------
  Attitude - A little thing that makes a BIG difference
----------------------------------------------------------------------------



"Ron de Bruin" <rondebruin@kabelfoon.nl> wrote in message
news:eea#K3vcDHA.456@TK2MSFTNGP10.phx.gbl...
> Dave posted this links today
>
> Debra Dalgleish's pictures at Jon Peltier's site:
> http://www.geocities.com/jonpeltier/Excel/Pivots/pivottables.htm
> And Debra's own site:
> http://www.contextures.com/xlPivot01.html
>
> John Walkenbach also has some at:
> http://j-walk.com/ss/excel/files/general.htm
> (look for Tony Gwynn's Hit Database)
>
> Chip Pearson keeps Harald Staff's notes at:
> http://www.cpearson.com/excel/pivots.htm
>
> MS has some at (xl2000 and xl2002):
> http://office.microsoft.com/downloads/2000/XCrtPiv.aspx
> http://office.microsoft.com/assistance/2002/articles/xlconPT101.aspx
>
>
>
> --
> Regards Ron de Bruin
> (Win XP Pro SP-1  XL2002 SP-2)
> www.rondebruin.nl
>
>
>
> "Ken Wright" <ken.wright@NOSPAMntlworld.com> wrote in message
news:O0OCpzvcDHA.2384@TK2MSFTNGP09.phx.gbl...
> > Pivot Table / Pivot Table / Pivot Table!!!!!!!!!!!!!!!!!!!!!!!!
> >
> > For an excellent intro to something that will make you go WOW!!!!!, wing your way on over to
Debra
> > Dalgleish's intro at:-
> >
> > http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm
> >
> > --
> > Regards
> >            Ken.......................    Microsoft MVP - Excel
> >                    Sys Spec - Win XP Pro /  XL2K & XLXP
> >
> > ----------------------------------------------------------------------------
> >   Attitude - A little thing that makes a BIG difference
> > ----------------------------------------------------------------------------
> >
> >
> >
> > "Holly Bodger" <holly.bodger@cma.ca> wrote in message
> > news:1dee01c372f5$3aab6190$a601280a@phx.gbl...
> > > I am trying to find an automated way of summing common row
> > > data. For example:
> > >
> > > ORIGINAL DATA
> > > Row1   123   John Smith
> > > Row2   456   John Smith
> > > Row3   678   John Smith
> > > Row4   324   Dave Jones
> > > Row5   678   Dave Jones
> > >
> > > DESIRED DATA
> > > Row1   1257  John Smith
> > > Row2   1002  Dave Jones
> > >
> > > I have several thousand rows and don't want to write
> > > something that is specific to the data that appears in the
> > > second column.
> > >
> > > Any ideas?
> >
> >
>
>


0
ken.wright (2489)
9/4/2003 4:05:32 PM
A pivot table is the answer to your question.  Click Data 
> PivotTable PivotChart > Next > Select the data range 
(this is usually done automatically) select Next.  From 
here it depends on which version of Excel you are using 
but you will need to click Layout or a dialog box is 
already open that says Layout.  Click and drag the "Name 
Field" to where it says ROW.  Then click and drag the Count
(let's call it Count) field you want to SUM into the area 
where it says DATA.  If it does not say "Sum of Count" 
double click that button looking item in the Data area of 
the dialog and you will see all kinds of ways you can 
display the data.  Select "SUM" Click OK then Click 
FINISHED.  And there you are!

Marty


>-----Original Message-----
>I am trying to find an automated way of summing common 
row 
>data. For example:
>
>ORIGINAL DATA
>Row1   123   John Smith
>Row2   456   John Smith
>Row3   678   John Smith
>Row4   324   Dave Jones
>Row5   678   Dave Jones
>
>DESIRED DATA
>Row1   1257  John Smith
>Row2   1002  Dave Jones
>
>I have several thousand rows and don't want to write 
>something that is specific to the data that appears in 
the 
>second column.
>
>Any ideas?
>.
>
0
msprenz (7)
9/4/2003 4:06:36 PM
That's works brilliantly! Thanks a million...

Holly

>-----Original Message-----
>A pivot table is the answer to your question.  Click Data 
>> PivotTable PivotChart > Next > Select the data range 
>(this is usually done automatically) select Next.  From 
>here it depends on which version of Excel you are using 
>but you will need to click Layout or a dialog box is 
>already open that says Layout.  Click and drag the "Name 
>Field" to where it says ROW.  Then click and drag the 
Count
>(let's call it Count) field you want to SUM into the area 
>where it says DATA.  If it does not say "Sum of Count" 
>double click that button looking item in the Data area of 
>the dialog and you will see all kinds of ways you can 
>display the data.  Select "SUM" Click OK then Click 
>FINISHED.  And there you are!
>
>Marty
>
>
>>-----Original Message-----
>>I am trying to find an automated way of summing common 
>row 
>>data. For example:
>>
>>ORIGINAL DATA
>>Row1   123   John Smith
>>Row2   456   John Smith
>>Row3   678   John Smith
>>Row4   324   Dave Jones
>>Row5   678   Dave Jones
>>
>>DESIRED DATA
>>Row1   1257  John Smith
>>Row2   1002  Dave Jones
>>
>>I have several thousand rows and don't want to write 
>>something that is specific to the data that appears in 
>the 
>>second column.
>>
>>Any ideas?
>>.
>>
>.
>
0
9/4/2003 5:03:07 PM
Reply:

Similar Artilces:

How to perform sum sum sum...
How to perform sumation within that particular item but the item is not unique...means item 1 has its own quantity and same goes to item 2...but in the same table... -- Message posted via http://www.accessmonster.com On Tue, 17 Apr 2007 05:28:12 GMT, "EMILYTAN via AccessMonster.com" <u33296@uwe> wrote: >How to perform sumation within that particular item but the item is not >unique...means item 1 has its own quantity and same goes to item 2...but in >the same table... Group By the item. For a more detailed answer, please post a more detailed question (with a des...

hiding rows
Hi, I want to use the conditional formating of a certain range of cells so that: if a cell in that range is empty (nothing is shown) hide the entire row. note: the values in the cells are copied from other cells in a different worksheet. If this is not possible with conditional formating can it be done with VBA and How? Not possible with CF Look here for a few ways http://www.rondebruin.nl/print.htm#Hide For example Hide Empty rows, Print and unhide the rows This example will loop through row 1:30 in "Sheet1" If every cell in column A:G is empty it will hide that row. Afte...

Summing up user defined results
Hi all - I'm new to VBA programming in Excel and so any help i'd totally love! I'm currently writing a function. Objective: user can select rows (do not have to be sequential). User clicks on button. UserForm appears with summed results from ONLY rows that he selected. What I have now, well it doesn't work: Sub Button6_Click() Dim i As Integer Dim totalNumbers As Integer Dim aRange As range For Each a In Selection.Areas 'MsgBox "Area " & i & " of the selection contains " & _ ' a.Rows.Count & " rows." &...

Replace variable row number in formulas
What I'm looking for is a macro that will replace the row number in a formula with a new number that I designate through an input box. Below is the code I have thus far. The problem lies in how I'm defining the integer portion of the formula that I want to replace - vbInteger (or vbLong) don't seem to work. Then finally I need to set Section 2 in a loop through Column CN. Any advice would be greatly appreciated! --------------------------------------------------------------------------------------------------------------------------- Sub UpdateFormulas_2() Dim LRowNumber ...

Multiple variables to sort and sum, return values<0 with sum refer
Can anyone help me with this one please? I have this table of data. I'd like to be able to write a formula(s) which sums the No according to date and code, but then only returns a sum value (with the code and date in the two adjacent cells) if there is a value greater than 0. Code Ref No Date 1 G/032/05/999 400600 212 19/03/2010 2 G/032/03/001/999 400500 50 19/03/2010 3 G/032/02/001/001 400400 170 19/03/2010 4 G/032/05/999 400600 315 19/03/2010 5 G/032/03/001/999 400500 300 19/03/2010 6 G/032/05/999 400600 202 19/03/2010 7 G/032/03/001/002 40...

sum subform to form
=Sum([Forms]![frmCamPledgeList].[frmCamPledgeListSub].[Amount Pledged]) Form (frmCamPledgeList) with subform frmCamPledgeListSub). Subform has a field named Pledge Amount. I want to put a field on the form that gives me a total of the subform's Amount Pledged. I have tried the above, but I get an #error in the unbound text box. Do you need any more info? Any thoughts on why this does not work? Thanks in advance, Scott -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200912/1 The proper syntax is =Sum([Forms]![frmCa...

Calculate sum of a field within the query
I have a query that returns the details for a failure record during a specified time period. Where if I search between 1/1/08 and 1/31/08 the query returns the records of all failure records during that period. The row shows the part number and the total parts failed for that record. I need the query to then sum the total part failures for the previous 12 months. So I would see the following: Record # | part # | parts failed | Origination Date| Total Parts failed past 12 months 44444 | x | 4 | 1/2/08 | 200 44445 | Y |1 ...

Deleting multiple rows from value list
Is there a way to delete multiple rows in a value list for a Text column? I have over 200 rows out of a value list that are duplicates that I need to delete. I can delete them row by row but I'd like to click on the 1st row and shift click on the last row that needs to deleted. Is this possible? Thanks in advance (from a Project newbie) Yes. Select then click on the cut icon. Try it. -Jack Dahlgren "Val" <Val@discussions.microsoft.com> wrote in message news:6596A51E-9C60-46A4-B9AB-AAB335A92906@microsoft.com... > Is there a way to delete multiple ro...

how to make this work if sum=5+n2 then sum becomes the value of s.
I want to get or create a formula in excel or access that allows me to keep a running total of my supplies ie... I have 2 pens, remove one and receive 2. answer in the cell becomes 3 pens then if I zero out the received cell the inventory cell still remembers that I have 3 pens not 4 pens (because I received 2 more pens) and visa versa when I zero out the received cell it remembers I have 3 pens not 1 pen(because I removed 1 pen) I believe the method you suggest is flawed. You have no audit trail. If the number on the sheet doesn't match your actual inventory, how will you figure out...

=SUM Ranges Do Not Update
I have a Excel 2000 spread sheet, with the following macro to insert new row. Sub InsertRow() ' ' Macro1 Macro ' Macro recorded 4/27/2004 ' 'GoTo label, MyString ActiveSheet.Unprotect Application.Goto Reference:="MyCell" ActiveCell.Select ActiveCell.EntireRow.Insert ActiveCell.Offset(-1, 0).Select ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlPasteAll Application.CutCopyMode = False ActiveCell.Select ActiveSheet.Protect DrawingObjects:=True, Contents:=True Scenarios:=True End Sub The problem is that in the "Mycell&qu...

Sum value between dates problem driving me mad!
Good Evening All, I am really struggling here, have tried, nested IF's including AND's but am a bit stumped. I am fairly familiar with Arrays, but cannot seem to combine all. I have a data table (as detailed below). (Hopefully, this should be 'pastable' into Excel - it works for me) I simply wish to calculate the expected totals per month. Any help & assistance with this would be most welcome. Cheers, Mathew Note, earliest Start is 01/04/03 Start Finish Day Rate Apr-2003 May-2003 Jun-2003 Jul-2003 Aug-2003 01/04/2003 23.00 01/04/2003 09/07/2003 23.00 ...

adding a row to a file will not add line to linked files
Is there a way to instruct linked sheets/workbooks to add a row when creating new rows on a master list workbook -- Regards Bilcat1 ...

Using the Common Feed List in Excel
Is it possible to collect items from the Common Feed List (RSS feeds) generated by IE into Excel? I would like to screen a bunch of feeds from Craigslist and eBay and none of the readers I've tried seem to do it very well. TIA ed I have used the IE application with Excel to read 1000's of webpages. Depending on the Website it can be very simple, not so simple, or very complicated. Not all webpages are the same even though the make look similar. I don't know what you mean by "to do it very well". The code may not be simple. I would have to see the U...

Conditional Sum Wizard
Currently I am setting up a sheet and I am using the conditional sum wizard for formulas. Once a formula is created the sytem won't let me copy or change a formula. If I try to copy (Copy, paste special) a formula to another cell, the formula won't work anymore. If I create a formula with the wizard and afterwards change one of the parameters, the formula does not work anymore. Can anyone give me a hint on how I can solve these issues? Thanks. Please don't multi-post - you have an answer elsewhere, relating to use of CSE. Pete On Jan 6, 9:23=A0am, MarcoKoenders <MarcoKo...

How to use outline data (grouped rows) in a protected worksheet?
I want to protect a sheet containing outline data in grouped rows. After protection, users cannot hide or display the rows using the expansion buttons in the margin. Is using outline data impossible in protected sheets? ...

Sum Question
I am using Excel 2000 I have the following very simple formula in column j Sheet1 =SUM(B5*H5) I have this copied all the way down to line 40. It shows 0 in all of the cells all the way down. I would like for there to be a way that the cell would not show anything in it UNLESS there was something that it was calculating. For example. I have entered data in column b and column h through line 10, but it still shows 0 in column J all the way down to line 40. I realize that it is showing these 0's b/c I have placed the formula there, but is there a way that it will still calculate but only ...

Sum sum sum
Let me describe my problem... I have a form with with item number with is unique and then i put another form (subform) to linked to record the number of item in and out.... This means a item can have many in and out transactions which make the item number not unique.. My problem is I want to calculate the sum of in quantity within the same item. I want the sum to be keep on increasing when the user enter the quantity. Example user key in 2 then new user key 4 and i want the total quantity to be 6... Please help.... No idea of what i am talking? I will explain... -- Message posted via http:/...

Lock sets of cells in a row and sort by date linked to each set
I have a set of qualifications linked to a name that need to be presented in a row A = Name B = Qual Type 1 C = Expiry Date (eg 29/01/12) D = Qual Type 2 E = Expiry Date (eg 17/12/12) F = Qual Type 3 G = Expiry Date (eg 25/06/11) H = Qual Type 4 (has no expiry date) I want to sort each qualification into 'date expiry' order keeping it connected to the relevant Qual Type. Therefore, I want Qual Type 3 with it's exipiry date to move into Column B & C, Qual Type 1 with it's exipiry date to move into Column D & E and Qual Type 2 with it's exipiry da...

HOWTO localize common dialogs
hi all! I have a core application with no resources. I have a couple of DLLs containing the resources (one for each language i need to support). Now: 1. system is win2k and i develop with visual c++ 6.0 2. the application uses mfc as shared dll 3. the dlls containing the resources use mfc as shared dll too. 4. everything has been built using appwizard. i exactly display the correct GUI for the chosen language. but i'm trying to get the localized mfc resources in order to have the same language also for the common dialogs (es. FileOpen, the buttons of AfxMessageBox, etc). I applied su...

Taking sum of a field from sub report
Hi On a sub report I have a field datetot which is the sum of two fields those two fields are in turn coming from two further sub reports of the sub report. How can I take the sum of datetot field to the main report? Thanks Regards See: Bring the total from a subreport onto a main report at: http://allenbrowne.com/casu-18.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "John" <John@nospam.infovis.co.uk> wrote in message news:OCWbU...

Rows to table
I use a sheet to store different modules with start and end time in a table (tab1). In the second sheet (tab2) I want to show all modules sorted in time (rows) and per day (columns). Tab1 should be used as sheet to modify data and Tab2 should be used to present a tabular overview per day. Now I import to Access and report in CrossTable but I hope I can create a simular effect in Excel. I can not figure out how to do this. Index and Match won't work because of multiple rows with same day or time. Any suggestions? Tab1: Day TimeStart TimeEnd Topic 1 9:00 10:15 Kickof...

find row index of a number in a list
I have a column of numbers, in no particular order. In the column next to it, I want to write a 1 next to the highest number, 2 next to the 2nd highest and so on. How can I do this? Thanks in advance, Andrew Trull wrote: > I have a column of numbers, in no particular order. > In the column next to it, I want to write a 1 next to the highest > number, 2 next to the 2nd highest and so on. How can I do this? > > > Thanks in advance, > > Andrew You would use the RANK command: =RANK(Cell, Array of Cells, Order), e.g. if your cells were A1:A20, in B1 you would write...

sum command do not display the result eg:=sum(c5:c8)-total not
sum command do not display the result eg:=sum(c5:c8)-total not display the result in the cell even when sigma is clicked the same What is displayed instead? If you see the formula, then format the cell as General (or anything but text) and reenter that formula. If you see the wrong answer, then maybe you have calculation set to manual. Tools|Option|calculation tab is how you'd change it in xl2003 menus. If that's not it, then maybe your values in those cells (C5:C8) aren't really numbers. Reformat that range as General (not text) and reenter the values. abbbalu wr...

How do I move ot next row in macro
I have an excel macro where I need to tell the selection to move down one row from the currently select row. What is the correct syntax to do that? James Lysaght "JamesL" <forgeddaboudit@spamblock.duh> wrote in message news:z11se.4667$hK3.4443@newsread3.news.pas.earthlink.net... >I have an excel macro where I need to tell the selection to move down one >row from the currently select row. > > What is the correct syntax to do that? Hi James, ActiveCell(2, 1).Select However, it is rarely necessary (and is usually inefficient) to make physical select...

Adding Sum
I made a query report with unique records to display,I don't want repeating item displayed in the report. What should I do to sum the other qty delivered in a single item. Item Qty Date Pork 50 08/01/05 60 08/10/05 20 08/15/05 should be Item Qty Date Pork 130 08/01/05 - 08/15/05 thanks & Godspeed. One way Sub Test() Dim iLastRow As Long Dim i As Long Dim dteMax As Date Dim dteMin As Date Dim...