comparing duplicates and removing them, stuck!

[kralgi]

Sub Find_Matches()
    Dim CompareRange As Variant, x As Variant, y As Variant

    Set CompareRange = Range("A2:A777")

    For Each x In Selection
        For Each y In CompareRange
            If x = y Then [ ??? ]
        Next y
    Next x
End Sub

---

the second column which im comparing to is B
there are a lot of more rows and values in column A (which is my compareRange).
the question is, what command should i put instead of the [???] so it will remove the duplicated right from the A row ?

[kralgi]

anyone? please?

[pravi1950@gmail.com]

one can use advance filter, copy unique
In Data menu you have advance filter(specify the range), Then give criteria range, next copy to range (left side of the pop up menu pls check for unique records only. You will be able to get unique records.

[arlu1201]

You mean you are comparing entries in Column A v/s Column B in the same sheet and then you want to delete the duplicates? Delete just the cells which match?

Do you have a sample that you can upload?

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[kralgi]

"You mean you are comparing entries in Column A v/s Column B in the same sheet and then you want to delete the duplicates? Delete just the cells which match?"

that's exactly what I meant!
but i want the duplicates to be removed -only- from column A.
Column B will stay with it's original values no matter what.

[arlu1201]

Can you please attach a sample file so its easier to work out a solution and test it?

[kralgi]

Thanks, but any code solution? that was my point (:

[arlu1201]

I asked if you could upload a sample file so i can help you quickly. The instructions are given in my 1st post.

[kralgi]

Here is the attached file
I just wanna check the duplicates between row A and C and remove the duplicated values -only- in row A (so row C will stay with it's original values).

[kralgi]

anyone? please?

[koi]

you can write this in column B1 and drag it till end of column A

=IF(ISERROR(MATCH(A1,$C$1:$C$13,0)),A1,"")

[arlu1201]

Try this code and let me know -

Option Explicit

Sub compare_cols()
Dim i As Long, lrow As Long, lrow1 As Long

Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "Temp"

With Worksheets("Sheet1")
    .Columns("A:A").Copy Worksheets("Temp").Columns("A:A")
    lrow = Worksheets("Temp").Range("A" & Rows.Count).End(xlUp).Row
    Worksheets("Temp").Range("B1:B" & lrow).Value = "A"
    
    lrow = .Range("C" & .Rows.Count).End(xlUp).Row
    .Range("C1:C" & lrow).Copy Worksheets("Temp").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End With

With Worksheets("Temp")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    lrow1 = .Range("B" & .Rows.Count).End(xlUp).Row
    .Range("B" & lrow1 + 1 & ":B" & lrow).Value = "B"
    
    .Sort.SortFields.Clear
    .Sort.SortFields.Add Key:=Range("A:A") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    .Sort.SortFields.Add Key:=Range("B:B") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("A:B")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    For i = 2 To lrow
        If .Range("A" & i).Value = .Range("A" & i + 1).Value And .Range("B" & i).Value <> .Range("B" & i + 1).Value Then
            .Range("A" & i).Value = ""
        End If
    Next i
    
    .Rows(1).Insert
    .Rows(1).AutoFilter field:=2, Criteria1:="A"
End With

With Worksheets("Sheet1")
    .Columns("A:A").ClearContents
    Worksheets("Temp").UsedRange.Columns(1).SpecialCells(xlCellTypeVisible).Copy Worksheets("Sheet1").Range("A1")
    .Sort.SortFields.Add Key:=Range("A:A") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With .Sort
        .SetRange Range("A:A")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End With
End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button