Identifying Duplicates and assigning a 'Rank'

[Welsh Mark3]

I have a file with 1331 account ID's, I can use the count function to identify if an ID is duplicated, is there a way to identify which of the duplicates is the primary duplicate?

For the purpose of this, the primary would be defined as appearing first in the order (row number)

 

[mumps]

Assuming the ID's are in column A starting in row 2, this macro will highlight in red the primary duplicate.

Sub FindPrimaryDup()
    Application.ScreenUpdating = False
    Dim Rng As Range, RngList1 As Object, RngList2 As Object, item As Variant, ID As Range
    Set RngList1 = CreateObject("Scripting.Dictionary")
    Set RngList2 = CreateObject("Scripting.Dictionary")
    For Each Rng In Range("A2", Range("A" & Rows.Count).End(xlUp))
        If Not RngList1.Exists(Rng.Value) Then
            RngList1.Add Rng.Value, Nothing
        Else
            RngList2.Add Rng.Value, Nothing
        End If
    Next Rng
    For Each item In RngList2
        Set ID = Range("A:A").Find(item)
        ID.Interior.ColorIndex = 3
    Next item
End Sub

 

[Fluff]

With a formula

Excel 2013/2016
	A	B
1	Hole ID
2	2018PJAC001	Primary
3	2018PJAC001
4	2018PJAC001
5	2018PJAC001
6	2018PJAC001
7	2018PJAC002	Primary
8	2018PJAC002
9	2018PJAC002
10	2018PJAC002
11	2018PJAC002
12	2018PJAC003	Primary
13	2018PJAC003
OrderCard

Cell Formulas
Range	Formula
B2	=IF(COUNTIF(A$2:A2,A2)=1,"Primary","")

 

[Welsh Mark3]

With a formula

Excel 2013/2016

	A	B
Hole ID
2018PJAC001	Primary
2018PJAC001
2018PJAC001
2018PJAC001
2018PJAC001
2018PJAC002	Primary
2018PJAC002
2018PJAC002
2018PJAC002
2018PJAC002
2018PJAC003	Primary
2018PJAC003

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

[TD="align: center"]11[/TD]

[TD="align: center"]12[/TD]

[TD="align: center"]13[/TD]

</tbody>

OrderCard​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B2[/TH]
[TD="align: left"]=IF(COUNTIF(A$2:A2,A2)=1,"Primary","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Works perfectly Thank you

 

[Fluff]

Glad we could help & thanks for the feedback