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!

Walmart	114
Walmart	852
Kmart	444
Kmart	5721
ABC Liquor	P874103
ABC Liquor	54984
Walmart	2574
Walmart	741
Kmart	88
Kmart	4555
ABC Liquor	28407
ABC Liquor	T456
Kmart	8730
Kmart	F987
Walmart	258
Walmart	114
ABC Liquor	54846
Kmart	2013
Walmart	289
Kmart	147
Walmart	963
ABC Liquor	221874
ABC Liquor	28407
Walmart	654
Kmart	2471
Walmart	369
ABC Liquor	R5874
ABC Liquor	56478

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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!