VBA Excel: Find duplicate values based on multiple criterias

Hello experts,

I have one the following information:

Local ID;      National ID;Continental ID
123;      N45;C12
124      N45      C12
125      N46      C18
122      N47      C20
123      N46      C18
124      N45      C17

I am looking for a macro to identify the various lines which have a National ID related to multiple Continental ID ex:
Local ID      National ID      Continental ID
123      N45      C12
124      N45      C12
125      N46      C18
122      N47      C20
123      N46      C18
124      N45      C17

N45 is related to the Contintenal ID C12 and C17 as a result the various lines which have a N45 need to be highlighted in red and copy in Sheet3 as done in my file attached.

Thank you very much for your help.
Duplicate-values.xlsx
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
you can use CountIF formula to do this.
0
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
in this example, do you want to highlight N46 as well?

>>125      N46      C18
>>123      N46      C18
0
LD16Author Commented:
Thank you for your comment. Nop as N46 has just one continental id C18 and this is not inconsistent. Even if there are multiple local id. The check need to be done just for national and continental id.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Ejgil HedegaardCommented:
Try this
Option Explicit

Sub FindDuplicates()
    Dim wsIn As Worksheet, wsOut As Worksheet
    Dim rwIn As Long, rwMax As Long, rwOut As Long
    Application.ScreenUpdating = False
    Set wsIn = Worksheets("Sheet1")
    Set wsOut = Worksheets("Sheet3")
    wsOut.Cells.Clear
    wsIn.Cells.Interior.Color = xlNone
    wsIn.Range("A1:C1").Copy wsOut.Range("A1")
    rwMax = wsIn.Range("A1").CurrentRegion.Rows.Count
    rwOut = 1
    For rwIn = 2 To rwMax
        If WorksheetFunction.CountIf(wsIn.Range("B2:B" & rwMax), wsIn.Range("B" & rwIn)) <> WorksheetFunction.CountIfs(wsIn.Range("B2:B" & rwMax), wsIn.Range("B" & rwIn), wsIn.Range("C2:C" & rwMax), wsIn.Range("C" & rwIn)) Then
            rwOut = rwOut + 1
            wsIn.Range("A" & rwIn & ":C" & rwIn).Copy wsOut.Range("A" & rwOut)
            wsIn.Range("A" & rwIn).EntireRow.Interior.Color = RGB(255, 0, 0)
            wsOut.Range("A" & rwOut).EntireRow.Interior.Color = RGB(255, 0, 0)
        End If
    Next rwIn
End Sub

Open in new window

See workbook.
Duplicate-values.xlsm
0
LD16Author Commented:
Working perfectly.
1-Just for my understanding could you please explain the logic of the formula:

WorksheetFunction.CountIf(wsIn.Range("B2:B" & rwMax), wsIn.Range("B" & rwIn)) <> WorksheetFunction.CountIfs(wsIn.Range("B2:B" & rwMax), wsIn.Range("B" & rwIn), wsIn.Range("C2:C" & rwMax), wsIn.Range("C" & rwIn))

Open in new window


2-If possible is there a way to automatically sort the various cells by continental ID this is what I got when I apply the sort after the macro record:

ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet3").Sort.SortFields.Add Key:=Range("B2:B8"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet3").Sort
        .SetRange Range("A1:C8")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Open in new window


3-And create Sheet3 if it doesn't exist.

Sorry for those additional requirements but I realize to add them after I test your solution.
0
Ejgil HedegaardCommented:
1: If the count of "National ID" are different from the count of ("National ID" and "Continental ID") then there are more than one "Continental ID" for the "National ID" we are looking at.

2: The recorded macro is what is used from Excel 2007 on, and it is possible to add many sort fields.
If there are up to 3 sorting criteria, I normally use the "old" method from Excel 2003.
It can be made in just one statement.
wsIn.Range("A1:C" & rwMax).Sort Key1:="Continental ID", Order1:=xlAscending, Header:=xlYes

Open in new window


3: Checking for Sheet3, and create if it is not there, is done by this
    For Each ws In Worksheets
        If ws.Name = "Sheet3" Then
            Set wsOut = ws
        End If
    Next ws
    If wsOut Is Nothing Then
        Set wsOut = Worksheets.Add(After:=wsIn)
        wsOut.Name = "Sheet3"
    End If

Open in new window

Duplicate-values.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author Commented:
Got it, thank you again for your help. The macro works perfectly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.