Automatically Combining Entries with Same Date

[ritland]

I currently log costs for our clients into an excel database. Each
client has their own worksheet which contains the following columns:

Date (A) - Postage (B) - Copies (C) - Fax (D) - Phone (E)

I am given cost sheets to input into the database that are not in
chronological order nor grouped by type. As a result, after I input
the information I find that there are often multiple entries for one
day and often more than one entry per category per day. For instance,
I will have rows 1, 5, and 10 with the same date but with different
costs in each ( R1 - $.40 Postage, R5 - $1.00 Copies, R10 $.30
Postage). What I would like to do is create a VB script that would
automatically run when each worksheet is selected that would do the
following:
(1) Find rows with the same date
(2) Combine the information from the matching rows into one entry (1B
+5B+10B, 1C+5C+10C, ect)
(3) Delete all rows with that date except for the summarized row

I am sure this is possible, but I lack the VB experience to create it.
I assume it will have to loop through an array but I dont know how it
would be set up. Any thoughts or know of any code that would do
something similar that I could modify? Thanks for all your help.

 

[Ken Johnson]

I currently log costs for our clients into an excel database. Each
client has their own worksheet which contains the following columns:

Date (A) - Postage (B) - Copies (C) - Fax (D) - Phone (E)

I am given cost sheets to input into the database that are not in
chronological order nor grouped by type. As a result, after I input
the information I find that there are often multiple entries for one
day and often more than one entry per category per day. For instance,
I will have rows 1, 5, and 10 with the same date but with different
costs in each ( R1 - $.40 Postage, R5 - $1.00 Copies, R10 $.30
Postage). What I would like to do is create a VB script that would
automatically run when each worksheet is selected that would do the
following:
(1) Find rows with the same date
(2) Combine the information from the matching rows into one entry (1B
+5B+10B, 1C+5C+10C, ect)
(3) Delete all rows with that date except for the summarized row

I am sure this is possible, but I lack the VB experience to create it.
I assume it will have to loop through an array but I dont know how it
would be set up. Any thoughts or know of any code that would do
something similar that I could modify? Thanks for all your help.

Try this macro out on a copy of one of your worksheets...

Option Explicit
Public Sub MERGE_SAME_COL_A_DATES()
Application.ScreenUpdating = False
Dim lnLastRow As Long
Dim I As Long
Dim J As Long
Dim K As Long
lnLastRow = Cells(Rows.Count, 1).End(xlUp).Row
'Start from top of rows with dates
'If headings are more than 1 row deep
'change the 2 to number of heading rows + 1
For I = 2 To lnLastRow
'Compare with rows from bottom of sheet
'If headings are more than 1 row deep
'change the 3 to number of heading rows + 2
For J = lnLastRow To 3 Step -1
'Don't compare same row
If J = I Then Exit For
'Test dates
If Cells(I, 1).Value = Cells(J, 1).Value Then
'Date are the same
'Add postage,copies,fax and phone values
For K = 2 To 5
Cells(I, K).Value = Cells(I, K).Value + _
Cells(J, K).Value
Next K
'Delete the lower row
Cells(J, 1).EntireRow.Delete
'Deleted row reduces lnLastRow by 1
lnLastRow = lnLastRow - 1
End If
'Compare next row up with the same upper row
Next J
'Test next upper row down
Next I
End Sub

Ken Johnson