Complex merge problem

[viralnerd2013]

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.

 

[Snakehips]

viralnerd2013,

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

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.

 

[viralnerd2013]

That worked like a charm. Thank you Tony!

 

[Snakehips]

viralnerd2013,

You're welcome.