# 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?

x
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.

Commented:
http://www.asap-utilities.com/
Commented:
Please take a look at my article here:

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

jppinto
Commented:
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
``````
Example.xls
Commented:
You can apply this formula to the whole column B

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

see attached

regards, barry
26925156.xlsx
Author Commented:
barryhoudini:

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

Experts Exchange Solution brought to you by