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
zyanjAsked:
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.

John-Charles-HerzbergCommented:
I think this might help you. FREE for Home and students
http://www.asap-utilities.com/
0
jppintoCommented:
Please take a look at my article here:

http://excel-user.blogspot.com/2009/10/highlight-duplicates.html

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

Sid

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)
            Else
                Set dupRange = Union(dupRange, ws.Range("B" & i))
            End If
        End If
    Next i
    
    dupRange.Interior.ColorIndex = 3
End Sub

Open in new window

Example.xls
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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

=COUNTIFS(B:B,B1,A:A,A1)>1

see attached

regards, barry
26925156.xlsx
0
zyanjAuthor Commented:
barryhoudini:

your sheet is exactly what I want but I don't follow how you got it though?
0
zyanjAuthor Commented:
after further review some lines are highlighted that should not be as well?
0
zyanjAuthor Commented:
but barryhoudini: you are the closest to what I am looking for any more thoughts?
0
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
0
DaveCommented:
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

http://www.experts-exchange.com/A_2123.html

Cheers

Dave
0
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.
Book1.xlsx
0
DaveCommented:
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

Cheers

Dave




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

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.