Complex merge problem

viralnerd2013

New Member
Hello Excel Gurus,

I am hoping you can help me with a macro that can handle this task:

If the cells in columns A, B, C, D, E, F, and G are identical in any number of rows, then add the values in columns H and I and report one row.

Input example
 Item Platform Visit Pos RefCov Ref Sub SubCov Frequency TEST NGS VISIT 660 37161 Ile Val 35961 0.9677 TEST NGS VISIT 660 37161 Ile Val 683 0.0184 TEST NGS VISIT 660 37161 Ile Val 374 0.0101

<colgroup><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Output
 Item Platform Visit Pos RefCov Ref Sub SubCov Frequency

<tbody>
</tbody>
 TEST NGS VISIT 660 37161 Ile Val 37018 0.9962

<tbody>
</tbody>

Any help will be greatly appreciated!

Thanks.

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
viralnerd2013,

Assuming that the rows that you wish to merge are always adjacent then perhaps try this...

Code:
``````Sub Merge()
Application.ScreenUpdating = False
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Do Until LastRow = 1
Test1 = ""
Test2 = ""
For c = 0 To 6
Test1 = Test1 & Range("A" & LastRow).Offset(0, c)
Test2 = Test2 & Range("A" & LastRow).Offset(-1, c)
Next c
If Test1 = Test2 Then
Range("H" & LastRow - 1) = Range("H" & LastRow - 1) + Range("H" & LastRow)
Range("I" & LastRow - 1) = Range("I" & LastRow - 1) + Range("I" & LastRow)
Range("I" & LastRow).EntireRow.Delete
End If
LastRow = LastRow - 1
Loop
Application.ScreenUpdating = True
End Sub``````

Hope that helps.

That worked like a charm. Thank you Tony!

viralnerd2013,

You're welcome.

Replies
8
Views
291

1,203,600
Messages
6,056,200
Members
444,850
Latest member
dancasta7

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?

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

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