Highlight Duplicates

Office 2007...

I would like to highlight duplicate item numbers based upon my customers number to the left. I have attached example. I have been doing them manually with the conditional formating > Highlight duplicates tool.

How can I do them all at once. Thanks in Advance.   Example.xlsx
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

I think this might help you. FREE for Home and students
Please take a look at my article here:


Not for Points but since I already worked for this here is the code. Sample also attached.


Sub Sample()
    Dim ws As Worksheet
    Dim LastRow As Long
    Dim dupRange As Range
    Set ws = Sheets("Highlighted")
    LastRow = ws.Range("B" & Rows.Count).End(xlUp).Row
    For i = 1 To LastRow
        If Application.WorksheetFunction.CountIf(ws.Range("B2:B" & LastRow), ws.Range("B" & i).Value) > 1 Then
            If dupRange Is Nothing Then
                Set dupRange = ws.Range("B" & i)
                Set dupRange = Union(dupRange, ws.Range("B" & i))
            End If
        End If
    Next i
    dupRange.Interior.ColorIndex = 3
End Sub

Open in new window

Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

barry houdiniCommented:
You can apply this formula to the whole column B


see attached

regards, barry
zyanjAuthor Commented:

your sheet is exactly what I want but I don't follow how you got it though?
zyanjAuthor Commented:
after further review some lines are highlighted that should not be as well?
zyanjAuthor Commented:
but barryhoudini: you are the closest to what I am looking for any more thoughts?
barry houdiniCommented:
By using COUNTIFS you are counting, for each row, how many rows there are with that combination of values in column A and B (including that row) so when the COUNTIFS formula returns a value >1 that means there are duplicates and the formatting is triggered. Could you give me an example where it doesn't work as required?

regards, barry
While you have a number of good answers already you can also automate this with my Duplicate Master addin. The advantage of the addin over most other approaches is that it will
 - cater for case insensitive seaches, ignoring whitespaces (including the CHAR(160)) and other string replacement options
 - work on multiple sheets, and match from one, to some to all columns



zyanjAuthor Commented:
I have attached another sheet. I am sorry bbut so far I am not seeing the solution that I desire.
Perhaps I am not being to clear for that I apologize..

Basically I selected each custromers items in a range and did the duplicate formatting. I am looking for a solution that looks exactly like the attached sheet.

Thanks so much.
If you run my addin at  http://www.experts-exchange.com/A_2123.html with the settings below then you get very close to this
- ie the whole matching row for a combined A&B match is highlighted, not just B
You could run the addin then remove the new formatting from column A, C onwards




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
zyanjAuthor Commented:
PERFECTION !!!!!!!!!
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
Printers and Scanners

From novice to tech pro — start learning today.