Another "finding duplicates" macro

[jlang11]

I seem to have lost my log in info and the site doesnt recognize my email address so a new account it is. I have a spreadsheet that has 14 columns and 50k+ rows, but I need to search for duplicates in 2 of the columns... but not just duplicates.... here is some sample data. One of the columns has a business name, and another has invoice numbers. I need to pick out the duplicate invoice numbers from the same vendor. I'm not particular if it highlights the duplicates in 1 row, both rows, adds a word in column 15 "Duplicate" or how it notes that its a dup. I already have a some code sorting and deleting rows before this code would come into play, and will most likely add more code to sort bringing the dups to the top.

I thought about maybe a nested if statement in column 15, sorted by vendor, then invoice number, something like if A2 = A3 AND B2 = B3, 1,0, but Id like to just add to the macro so its one smooth processes instead of running one macro, manually adding an IF statement, then running another macro.

Any help is greatly appreciated!



[TABLE="width: 161"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]Walmart[/TD]
[TD]114[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]852[/TD]
[/TR]
[TR]
[TD]Kmart[/TD]
[TD]444[/TD]
[/TR]
[TR]
[TD]Kmart[/TD]
[TD]5721[/TD]
[/TR]
[TR]
[TD]ABC Liquor[/TD]
[TD]P874103[/TD]
[/TR]
[TR]
[TD]ABC Liquor[/TD]
[TD]54984[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]2574[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]741[/TD]
[/TR]
[TR]
[TD]Kmart[/TD]
[TD]88[/TD]
[/TR]
[TR]
[TD]Kmart[/TD]
[TD]4555[/TD]
[/TR]
[TR]
[TD]ABC Liquor[/TD]
[TD]28407[/TD]
[/TR]
[TR]
[TD]ABC Liquor[/TD]
[TD]T456[/TD]
[/TR]
[TR]
[TD]Kmart[/TD]
[TD]8730[/TD]
[/TR]
[TR]
[TD]Kmart[/TD]
[TD]F987[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]258[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]114[/TD]
[/TR]
[TR]
[TD]ABC Liquor[/TD]
[TD]54846[/TD]
[/TR]
[TR]
[TD]Kmart[/TD]
[TD]2013[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]289[/TD]
[/TR]
[TR]
[TD]Kmart[/TD]
[TD]147[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]963[/TD]
[/TR]
[TR]
[TD]ABC Liquor[/TD]
[TD]221874[/TD]
[/TR]
[TR]
[TD]ABC Liquor[/TD]
[TD]28407[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]654[/TD]
[/TR]
[TR]
[TD]Kmart[/TD]
[TD]2471[/TD]
[/TR]
[TR]
[TD]Walmart[/TD]
[TD]369[/TD]
[/TR]
[TR]
[TD]ABC Liquor[/TD]
[TD]R5874[/TD]
[/TR]
[TR]
[TD]ABC Liquor[/TD]
[TD]56478[/TD]
[/TR]
</tbody>[/TABLE]

 

[Captain_Conman]

Try this one...

Sub Duplicates()


Dim LastRow As Long
LastRow = ActiveSheet.Range("A1").CurrentRegion.Rows.Count
    Cells.Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("B2:B" & LastRow) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:O" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With


Dim Cell As Variant


For Each Cell In Range("A2:A" & LastRow)


Dim rng As Range
Set rng = Range(Cell, Cell.Offset(1, 0))


If Application.WorksheetFunction.CountIf(Range("A2:A" & LastRow), Cell) > 1 Then
    If Cell.Offset(0, 1).Value = Cell.Offset(1, 1).Value Then
    rng.EntireRow.Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    End If
    End If
Next


End Sub

MickG was able to help me a lot with some complex duplication matches which you can find here... This may or may not be helpful. He offered some awesome help! https://www.mrexcel.com/forum/excel-questions/1065716-highlight-rows-based-sum-multiple-cells.html

 

[jlang11]

Thank you! I will give this a shot in a little bit!