CODEWITHSUNDEEP
excelexcel-formulavba
mani_nz
mani_nz

Reputation: 5602

Compare 2 columns and remove duplicates without shifting

I have this below datasheet,

COLA        COLB
ABC          10
ABC          10
ABC          15
XYZ          10
XYZ          15
XYZ          15

I want to compare both the columns and if they are duplicate I want to remove blank the cell, I don't want to shift. For example like this,

   COLA        COLB
    ABC          10

    ABC          15
    XYZ          10
    XYZ          15

How can I do this in excel?

Cheers!!

Upvotes: 1

Views: 819

Answers (2)

Rosetta
Rosetta

Reputation: 2725

no vba method

Step 1 identify rows to remove using formula.

place this formula at C2 and fill down.

=IF(COUNTIFS(A$2:A2,A2,B$2:B2,B2)=1,1,"REMOVE")

enter image description here

Step 2 Filter for "REMOVE" only

enter image description here

Step 3 Select entire rows and just press DEL button, then remove filter.

enter image description here

Upvotes: 3

Thomas
Thomas

Reputation: 111

Try this. 

Option Explicit
Dim i, j, count, lastrow As Integer
Dim number As Long

Sub delete_duplicates()
    lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
    For i = 1 To lastrow
        number = Cells(i, 2)
        For j = 1 To lastrow
            If number = Cells(j, 2) Then
                count = count + 1
                If count > 1 Then
                    Cells(j, 2) = ""
                End If
            End If
        Next j
    count = 0
    Next i
End Sub

Upvotes: 1

Related Questions