Macro to remove duplicates base on other cell's valud

allgonzo

New Member
Joined
Jul 25, 2010
Messages
32
I am looking for a macro that will look for duplicates in a specific column then delete the duplicate if it meets the criteria of a separate column. So for example, column a has names, column b has numbers. Column c has specifics values that need to be check in order before deleting the rows in column b.

For example this is the beginning sheet

bob, 1, (0)
bob, 1, (-1)
tom, 2, (0)
tom, 2, (-1)
rick, 1, (-1)
rick, 1, (-2)

I want the macro to come up with

bob, 1, (0)
tom, 2, (0)
rick, 1, (-1)

Essentially deleting rows if its a duplicate in column b and if column c has the highest number. I hope this made sense. Thanks in advance for any suggestions.
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Try this:
Code:
Option Explicit

Sub RemoveLowerDupes()
Dim LR As Long

LR = Range("A" & Rows.Count).End(xlUp).Row

    Columns("A:C").Sort Key1:=Range("A1"), Order1:=xlAscending, _
                        Key2:=Range("B1"), Order2:=xlAscending, _
                        Key3:=Range("C1"), Order3:=xlDescending, _
                        Header:=xlGuess, OrderCustom:=1, _
                        MatchCase:=False, Orientation:=xlTopToBottom, _
                        DataOption1:=xlSortNormal, _
                        DataOption2:=xlSortNormal, _
                        DataOption3:=xlSortNormal
    
    With Range("D2:D" & LR)
        .FormulaR1C1 = "=IF(AND(RC1=R[-1]C1,RC2=R[-1]C2), ""x"", 1)"
        .SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete xlShiftUp
        .ClearContents
    End With

End Sub
 

allgonzo

New Member
Joined
Jul 25, 2010
Messages
32
Thanks for the quick response. When I ran the macro, the end result was:

bob, 1, (0)
rick, 1, (-2)
tom, 1, (-1)

I was hoping for
bob, 1, (0)
rick, 1, (-1)
tom, 1, (0).

I need the macro to delete a duplicate only if its the lowest number on column C. This is a great start. thanks in advance for any assistance.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Excel Workbook
ABC
1bob10
2bob1-1
3tom20
4tom2-1
5rick1-1
6rick1-2
BEFORE
Excel Workbook
ABC
1bob10
2rick1-1
3tom20
AFTER


It works for me perfectly. Maybe your 3rd column isn't really all numeric values? Those parentheses need to "not really there", rather there based on the cell formatting.
 

allgonzo

New Member
Joined
Jul 25, 2010
Messages
32

ADVERTISEMENT

It must have been the parentheses. I appreciate the help. Thank you.
 

allgonzo

New Member
Joined
Jul 25, 2010
Messages
32
Now that I applied the macro to a real life example, I realized I did not explain the situation properly. I apologize for my error. The macro worked wonderfully, it deleted too many rows. example below:

Start

bob, 45, 0
bob, 45, -1
bob, 45, -2
bob, 50, -1
bob, 50, -2
bob, 50, -3

End

bob, 45, 0
bob, 50, -1


Start with 3-4 duplicates in column B. The end result is to delete the duplicates in column b, based on the lowest number on column c, but not delete any duplicates in column a.

I hope this makes sense. Again, truly appreciate the assistance.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012

ADVERTISEMENT

On the data you provided, the macro performed properly:
Excel Workbook
ABC
1bob450
2bob45-1
3bob45-2
4bob50-1
5bob50-2
6bob50-3
BEFORE
Excel Workbook
ABC
1bob450
2bob50-1
3
4
5
AFTER
 

allgonzo

New Member
Joined
Jul 25, 2010
Messages
32
Real life I have 18 columns (A to R). Row one is the header (column names). Column D has the names, Column H has the number and Column N has the 0, -1, -2. I used the macro above and changed the columns to fit this example, but when I ran it, it only leaves one of the names regardless of the number. What I am looking for is for the Macro to delete any duplicates rows that has the same number (H) and the same name (D) based on the highest number on Column N (0 or -1). If the rows contain the same information in columns D, H and N, then it only leaves one row. I tried to alter the macro above to meet the criteria but I am missing something. I appreciate any thoughts. Sorry for multiple postings on the same question.
 

jbeaucaire

Well-known Member
Joined
May 8, 2002
Messages
6,012
Try this:
Code:
Option Explicit

Sub RemoveLowerDupes()
Dim LR As Long

LR = Range("D" & Rows.Count).End(xlUp).Row

    Columns("A:Z").Sort Key1:=Range("D2"), Order1:=xlAscending, _
                        Key2:=Range("H2"), Order2:=xlAscending, _
                        Key3:=Range("N2"), Order3:=xlDescending, _
                        Header:=xlYes, OrderCustom:=1, _
                        MatchCase:=False, Orientation:=xlTopToBottom, _
                        DataOption1:=xlSortNormal, _
                        DataOption2:=xlSortNormal, _
                        DataOption3:=xlSortNormal
    
    With Range("AA2:AA" & LR)
        .FormulaR1C1 = "=IF(AND(RC4=R[-1]C4,RC8=R[-1]C8), ""x"", 1)"
        .SpecialCells(xlCellTypeFormulas, 2).EntireRow.Delete xlShiftUp
        .ClearContents
    End With

End Sub
 

allgonzo

New Member
Joined
Jul 25, 2010
Messages
32
Like magic. Thank you so much for your assistance. It just saved us hours. Again, thanks
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,161
Messages
5,509,526
Members
408,738
Latest member
mkarhoff

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top