0

I found a way to my problem, I just added an extra column to combine data in multiple cells.

Like =B1&C1&E1 filled this down to lastrow in column G and searched for duplicates in this row.

I know I asked this Question in 2016 but better late than never i guess.

I use this script, but this only checks for one column if the row is duplicate or not.

How do i modify this to check for multiple columns (two is fine).

Sub merge_duplicates()
Dim count, count_1, found_str
count = 2
Do Until Range("A" & count) = ""
found_str = 0
For count_1 = 1 To count - 1
If InStr(1, Range("E" & count), Range("E" & count_1)) > 0 Then
Range("D" & count_1) = Range("D" & count_1) + Range("D" & count)
found_str = 1
End If
Next
If found_str = 1 Then
Rows(count).Delete
Else
count = count + 1
End If
Loop
End Sub

Example

Thanks in advance.

Smith James
  • 13
  • 1
  • 5

1 Answers1

0

This way is so much faster, first look for the last row in range, then remove duplicates at it. (Faster than looping around)

Sub test()

    Dim lastrow As Long

    With ThisWorkbook.Worksheets("Sheet1")
        If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
            lastrow = .Cells.Find(What:="*", _
                          After:=.Range("A1"), _
                          Lookat:=xlPart, _
                          LookIn:=xlFormulas, _
                          SearchOrder:=xlByRows, _
                          SearchDirection:=xlPrevious, _
                          MatchCase:=False).Row
        Else
            lastrow = 1
        End If

        'Array(1, 2, 16) means 1 - for A, 2 for B and 16 for P columns
        .Range("A1:P" & lastrow).RemoveDuplicates Columns:=Array(1, 2, 16), _
            Header:=xlYes
    End With
End Sub

Answered here: excel: check for duplicate rows based on 3 columns and keep one row

Community
  • 1
  • 1
KodornaRocks
  • 425
  • 3
  • 14
  • Works good for deleting the duplicates but this doesn't sum up column "D" – Smith James Mar 08 '16 at 13:30
  • Just change the Columns in the .RemoveDuplicates, to take from A to D change the Columns:Array(1,2,16) to Columns:=Array(1, 2, 3, 4) – KodornaRocks Mar 08 '16 at 14:25
  • Ups, now I got it, It's easy to loop through and do by hand, just try it yourself, later I come here and help again if you could'nt find some way out – KodornaRocks Mar 08 '16 at 14:27
  • Doing it by hand is not a good option because i have to work with 70k+ data. That would take quite some time. – Smith James Mar 09 '16 at 22:35