Results 1 to 7 of 7

Thread: Delete Duplicates with three more requirements

  1. #1

    Delete Duplicates with three more requirements



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Dear all here,

    First of all thank you very much for your previous help!! This Forum has been and is still of a great help for me and I am learning a lot thank to you!

    I am asking now regarding a VBA (Macro code) for Deleting Duplicates

    There is an option in Excel ("Remove Duplicates") and it does it in the number of cells selected.
    It also removes the duplicate the is in the lower position.

    Is there a VBA code, or Macro that
    1. DELETES DUPLICATES
    2. FROM TWO COLUMNS
    3. ONLY OF THE SELECTED CELLS
    4. AND CHOOSING WHICH OF THE TWO I WOULD LIKE TO REMOVE (upper or lower)?


    THANK YOU VERY MUCH!

    For example, I would like to remove duplicates of columns 2 and 3. Therefore Anna and Dandys lines are duplicates and Solomon and Kisas lines are also duplicates (Dandy and Kisas' lines would be deleted)
    column1 column2 column3
    Anna
    001 01
    Martha 002 02
    Solomon 006 02
    Dandy 001
    01
    Patricia 003 01
    Frank 004 03
    Sarah 004 04
    Laurent 005 05
    Kisa 006
    02

  2. #2
    Dear Excelforum community!

    I am just checking whether this message has reached to you...

    I would really appreciate help in this or, if not possible, a hint where to find it.

    Thank you once more!

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    Albita09 how do you determine what record to keep?
    In your example it it just happens to be, keep the first record, the next one that is a duplicate is deleted.
    Are there only ever going to be 2 duplicates? For your choice to pick to keep the upper or lower?
    Do you manually want to watch this run and choose every time it catches a duplicate, or create a list of the duplicate values to view and manually copy later?
    Any other criteria would be helpful, as the easiest way to do this will be to sort the entire list by column 2 and 3 to do comparisons.

  4. #4
    Dear Simi,

    First of all thank you very much for your answer!

    Actually, I need it much easier. The might be more than 1 duplicate of some elements, however I am TOTALLY OK when we keep the upper row ("Remove Duplicates" Excel Button does that") Therefore we could actually forget about condition number 4.

    Actually what I would like the Macro to do is to run EXACTLY what the button " Remove Duplicates" does. But not going manually with the mouse to the button but running it with a macro. (The idea behing is to merge this macro later on with other two and get a whole process run at once)

    Is it clear now? Do you want me to re-explain anything?

    Thank you VERY VERY VERY MUCH or your help in advance!

    A.

  5. #5
    Dear Simi,

    Once again, I have been doing some research online and this is the furhter I have gotten:

    Sub Macro1()
    '
    ' Macro1 Macro
    '
    Dim count
    For count = Selection.Row To Selection.Row + Selection.Rows.count - 1
    '
    Range("C" & count).Select
    ActiveSheet.Range("C" & count).RemoveDuplicates Columns:=Array(2, 3), Header _
    :=xlNo

    Next
    End Sub

    I am selecting some rows of column "C" but I ignore the number therefore I would like the macro to run for all the selected ones. A macro will do two more steps before the removing duplicates.
    The third step is this one and I would like the Macro to REMOVE DUPLICATES as per columns "D" and "E", eliminating the WHOLE.
    Exactly as the button "Removing Duplicates" does.

    This code is a copy paste of what I get recording the Macro that results of running it with the button however it always uses definite columns and rows so I ahve use the line

    '
    Dim count
    For count = Selection.Row To Selection.Row + Selection.Rows.count - 1

    That in other macros helped me to run things on the selected cells.

    However I have tried to run this code (that is for sure worng) and I get the Runtime Error '5' Invalid procedure call or argument

    Since I do not know how to rpogram in VBA I wonder if giving you this first research would be easier to help me...

    Thank you!!!

  6. #6
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    Albita09

    To accomplish what you want you need only the following.

    Code:
    Sub Simi_Remove_duplicates()
        ActiveSheet.Range(Cells(Selection.Row, Selection.Column), Cells(Selection.Row + Selection.Rows.count, Selection.Column + Selection.Columns.count)).RemoveDuplicates Columns:=Array(2, 3), Header _
            :=xlNo
    End Sub
    to remove the duplicates in the fashion you want you needed to select more than just column "C". I am simply using the selection properties to identify the range by cell number, instead of cell name. cells(1,1) refers to cell A1. cells(1,2) refers to cell B2.

  7. #7
    That is great Simi!

    Its seems to work perfectly!

    However you are right. I might no be able to incorporated of a bigger flow of macro this one nees to select all of the columns.

    I am going to do some more tires and I will get back to you if any questions!

    THANK YOU!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •