Merging and adding rows

C

corbie82

New Member
Joined
Jun 7, 2014
Messages
23
Hi all


im one formula away from getting my spreadsheet to look and do everything it needs to do.


I have 2 tabs old system and new system and a formula that copies all the data over and sorts them in a third tab. In the 3rd tab in column A is their name. Everybody should have 2 entries in the 3rd tab.
The data on how many calls they took is from column G to colomun U and the layout is identical from both the tabs. I need a vba formula that will combine the numbers from the row and then delete one of the entries like so.


This is what it looks like now
[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Monday[/TD]
[TD]Tuesday[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Wednesday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Thursday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"]Friday[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Saturday[/TD]
[TD]Sunday[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr old[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr old[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr new[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr new[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr excel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr excel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]This[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]is[/TD]
[TD]how[/TD]
[TD]i[/TD]
[TD]would[/TD]
[TD]like[/TD]
[TD]it to[/TD]
[TD]look[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr old[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr new[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr Smith[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mr Excel[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I get the feeling this is easy to do or its going to be a nightmare.
 
Sort by date Sort by votes
don't know if I got the columns right or not. But give it a try.

Code: 
Sub summarize()
Dim ary() As Variant
    With Sheets("Sheet3") 'Edit sheet name
        .Range("A2", .Cells(Rows.Count, 1).End(xlUp)).AdvancedFilter xlFilterCopy, , .Range("ZZ1"), True
        ary = Application.Transpose(.Range("ZZ2").Resize(.Range("ZZ1").CurrentRegion.Rows.Count - 1))
        .Range("ZZ1").CurrentRegion.Delete
        For i = LBound(ary) To UBound(ary)
            Set fn = .Range("A:A").Find(ary(i), , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    fAdr = fn.Address
                    For j = 7 To 21
                        fn.Offset(, j) = Application.SumIf(.Range("A:A"), ary(i), .Columns(j))
                    Next
                End If
                Set fn = .Range("A:A").FindNext(fn)
                If Not fn Is Nothing And fn.Address <> fAdr Then fn.EntireRow.Delete
        Next
    End With
End Sub

I strongly suggest you use a copy of your file and not the original.
 
Last edited:
Upvote 0
HI

Thanks for the code. It seems to do the removing part fine but it does do the adding bit right. Before i run the macro the sum total of the first column is 1263. After i run your program the total is 753. Just to recap it should combine the totals in the 2 rows and then remove the duplicate one. Any suggestions ?
 
Upvote 0
I tweaked this a little. had it starting in the wrong column. see if this works better

Code: 
Sub summarize()
Dim ary() As Variant
    With Sheets("Sheet3") 'Edit sheet name
        .Range("A2", .Cells(Rows.Count, 1).End(xlUp)).AdvancedFilter xlFilterCopy, , .Range("ZZ1"), True
        ary = Application.Transpose(.Range("ZZ2").Resize(.Range("ZZ1").CurrentRegion.Rows.Count - 1))
        .Range("ZZ1").CurrentRegion.Delete
        For i = LBound(ary) To UBound(ary)
            Set fn = .Range("A:A").Find(ary(i), , xlValues, xlWhole)
                If Not fn Is Nothing Then
                    fAdr = fn.Address
                    For j = 6 To 20
                        fn.Offset(, j) = Application.SumIf(.Range("A:A"), ary(i), .Columns(j + 1))
                    Next
                End If
                Set fn = .Range("A:A").FindNext(fn)
                If Not fn Is Nothing And fn.Address <> fAdr Then fn.EntireRow.Delete
        Next
    End With
End Sub
 
Upvote 0
Strange i get this error message now in the code although this code doesn't seem to have changed compared to the old one

I get run time error 5
invalid procedure call or agument


Set fn = .Range("A:A").FindNext(fn)
If Not fn Is Nothing And fn.Address <> fAdr Then fn.EntireRow.Delete
Next
End With
End Sub
 
Upvote 0
corbie82 said:
Strange i get this error message now in the code although this code doesn't seem to have changed compared to the old one

I get run time error 5
invalid procedure call or agument


Set fn = .Range("A:A").FindNext(fn)
If Not fn Is Nothing And fn.Address <> fAdr Then fn.EntireRow.Delete
Next
End With
End Sub
Click to expand...

I can't duplicate the error. The code runs as intended for me, without error. You can open the VBE, click anywhere inside the procedure and use function key F8 to step through the code line by line to see exactly where the code breaks down in relation to your data If you diminish the size of the VBE window so you can overlay it on the Excel worksheet while you step through, you see exactly what the code is doing and when. If you can pin point the place and condition that produces the error, I can probably fix it.
 
Upvote 0
I thought it might be that you had more than two rows of the same name and that deleting one of the rows would temporarily kill the variable fn, but I added a third row with a duplicate name and it did not error on that either, so I still don't know what would cause the error. The code appears to be valid.
 
Upvote 0
My test set up is in col A of sheet 3 a list of duplicate names (2 each). In Range G2:U7 I have numerical data, no blanks. When I run the code, it adds the values for each set of duplicates, deleting one of the rows as each one set is completed. Is that basically what you wanted to do with your sheet?
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

muhammad susanto
  • Solved
Extract Data From Single Cell [merged] to Multiple Column
2
Replies
12
Views
164
Peter_SSs
Peter_SSs
D
  • Question Question
Generate unique subsets with even distribution.
Replies
6
Views
160
Kaper
Kaper
S
  • Solved
Five Values that Equal a Sum Value
2
Replies
12
Views
227
ststern45
S
R
  • Question Question
Required Transposed Values instead of Row Items of Duplicate Invoice no with Each GSTIN which is displaying the SUM of 12+18+28
Replies
1
Views
113
rk_rk
R
D
  • Question Question
Formula leaves the word False but would like 0
Replies
3
Views
158
offthelip
O

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top