Summing data in one column based on date in another column

[Jon1234]

Hi

I need something that would sum up column B only when it shared a column A
name...

e.g.

A3= Tom B3= $10
A4= Jim B4 = $5
A5= Tom B5 = $5
A6= Jim B6= $12
If possible the macro could generate a pdf that would display

Tom = $15
Jim = $17

This would be needed for multiple names (over 20), and varying currency.

Thank you for your time

 

[Chip Pearson]

This is well-suited for a Pivot Table.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[JLGWhiz]

This assumes that you have a list of names in column D for all members of the
group. You can put the list in any column and adjust the code accordingly.
It will find all occrrences of a name in column A and add all values for that
name which it places in Column E adjacent to that name for Column D.

You can then copy that data to a new sheet and create a PDF file if needed.

Sub addemup()
Dim lr As Long, c As Range, nm As Range, mySum As Double
lr = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row
For Each c In Range("D2" & lr)
For Each nm In Range("A2:A" & _
Cells(Rows.Count, 1).End(xlUp).Row)
If nm = c Then
mySum = mySum + nm.Offset(0, 1).Value
End If
Next
c.Offset(0, 1) = mySum
Next
End Sub

 

[Leith Ross]

Hello Jon1234,

Here is another method, though longer, that will provide you with
flexibility. You can set the starting row, which sheet the data is on,
and the summary sheet. The original currency formats are saved and later
restored for each entry on the summary sheet.

Code:
--------------------

Sub SumByName()

Dim DSO As Object
Dim DstWks As Worksheet
Dim Item, Key, Keys
Dim LastRow As Long
Dim NumFormats() As String
Dim R As Long
Dim Rng As Range
Dim SrcWks As Worksheet
Dim StartRow As Long

'Specify the Source and Destination worksheets
Set SrcWks = Worksheets("Sheet1")
Set DstWks = Worksheets("Sheet2")

'Determine the data table's size and range
With SrcWks
StartRow = 2
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
LastRow = IIf(LastRow < StartRow, StartRow, LastRow)
Set Rng = .Range(.Cells(StartRow, "A"), .Cells(LastRow, "B"))
End With

'Create a Dictionary object
Set DSO = CreateObject("Scripting.Dictionary")
DSO.CompareMode = 1

'Add the names and amounts to the Dictionary
For R = StartRow To LastRow
Key = Rng.Cells(R, 1)
Item = Rng.Cells(R, 2)
If DSO.Exists(Key) Then
Item = DSO(Key)
DSO(Key) = Item + Rng.Cells(R, 2)
Else
DSO.Add Key, Item
ReDim Preserve NumFormats(DSO.Count)
'Save the number format for this cell
NumFormats(DSO.Count - 1) = Rng.Cells(R, 2).NumberFormat
End If
Next R

'List the names and amount on the Destination worksheet
Keys = DSO.Keys
For I = 0 To DSO.Count - 1
R = StartRow + I
DstWks.Cells(R, "A") = Keys(I)
'Restore the original number format for the amount
With DstWks.Cells(R, "B")
.NumberFormat = NumFormats(I)
.Value = DSO(Keys(I))
End With
Next I

'Free the object and memory
Set DSO = Nothing

End Sub

--------------------

ADDING THE MACRO
1. *Copy* the macro above pressing the keys *CTRL+C*
2. Open your workbook
3. Press the keys *ALT+F11* to open the Visual Basic Editor
4. Press the keys *ALT+I* to activate the *Insert menu*
5. *Press M* to insert a *Standard Module*
6. *Paste* the code by pressing the keys *CTRL+V*
7. Make any custom changes to the macro if needed at this time.
8. *Save the Macro* by pressing the keys *CTRL+S*
9. Press the keys *ALT+Q* to exit the Editor, and return to Excel.

TO RUN THE MACRO...
To run the macro from Excel, open the workbook, and press *ALT+F8* to
display the *Run Macro Dialog*. Double Click the macro's name to *Run*
it.

Sincerely,
Leith Ross

 

[Jon1234]

This worked wonderfully...except I forgot one key aspect...in a few
circumstances there is an opposite payment type.

Column A= name
Column B= purchase type (buy or sell)
Column C = $$

So I need it to not only sort it by name but by name and purchase type...

Tom Sell 5
Tom Buy 10
Tom Buy 5
Jim Buy 5
Jim Buy 10
Jim Sell 1

Tom Sell 5
Tom Buy 15
Jim Buy 15
Jim Sell 1

I'll be tinkering with your original script, and hopefully get it to
work...the sum populating in E for Sale and F for Buy would work just fine
assuming the list is in D.

Thanks again.