Duplicates using 2 columns

[psilzle]

I know how to check a column for a duplicate entry. Is there a way to
check and highlight duplicates within 2 cells. Here is an example:

Sales order Line #
1234 1
1234 2
1234 2
1234 3
4321 1
4321 2

I would want the 2 lines with sales order 1234 and line 2 to be
highlighted. The rest would be fine.

Any ideas?

Thank you,

Paul

 

[RagDyeR]

Check out a multitude of web pages on handling duplicates from Chip Pearson.

Scroll down to the D's:

http://www.cpearson.com/excel/topic.htm


--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

I know how to check a column for a duplicate entry. Is there a way to
check and highlight duplicates within 2 cells. Here is an example:

Sales order Line #
1234 1
1234 2
1234 2
1234 3
4321 1
4321 2

I would want the 2 lines with sales order 1234 and line 2 to be
highlighted. The rest would be fine.

Any ideas?

Thank you,

Paul

 

[Erny]

Let's say column A contains your "Sales order" and column B your "Line #",
cell C1 the Sales order you wish to highlight, and cell D1 the line that
needs to be watched.

If you higlight columns A & B and use Conditional Formatting with the
following formula as condition 1:

=($A1=$C$1)*($B1=$D$1)=1

and use the background colour of your choice.

Kind regards,
Erny

 

[psilzle]

Erny,

I am not looking to watch a certian entry, I want a conditinaol
format to tell me when there is a duplicate entry that has not only
the same sales order # but also the same line # also. So if there are
2 entries anywhere in those 2 columns that contain the same sales
order and same line number they would be highlighted.

Thanks,
Paul

 

[Erny]

Hi Paul,

The formula should do it, if you use conditional formatting when you have
marked both columns entering it on the line for the condition. It should
replicate itself automatically to highlight all duplicates where the two
conditions are met.
Just select the 2 columns A and B with your mouse, then choose "Conditional
Formatting", click on formula and copy/paste the formula underneath, then
specify the background you wish if the condition is true. Excel should do
the rest.
I'm surprised if it doesn't do it, please let me know.

Erny

 

[psilzle]

I realize that I was not clear on what I was looking for. The formula
you gave me does work for finding a cetian duplicate. I need it to
find all duplicates. If I were to to have SO# 1234 and line # 2 in
muliple lines and also SO# 9999 line # 4 in multiple lines and so on,
I would need it to show all of them. The fomula you gave will only
show me certian ones, correct?

Thanks,
Paul

 

[Bruno Campanini]

I realize that I was not clear on what I was looking for. The formula
you gave me does work for finding a cetian duplicate. I need it to
find all duplicates. If I were to to have SO# 1234 and line # 2 in
muliple lines and also SO# 9999 line # 4 in multiple lines and so on,
I would need it to show all of them. The fomula you gave will only
show me certian ones, correct?

I have a small routine (Sub) which cycles through your range and
selects (using a background color) the various type of duplicates.
Not all the same time, you must click a button several times to have
all existing ones: every click one type of duplicates.

Do you think it's ok for you?

Bruno

 

[psilzle]

I could give it a try. It is better then the nothing I have.

Thanks!

Paul

 

[Bruno Campanini]

I could give it a try. It is better then the nothing I have.

Thanks!

Paul

Here it is:

Sub SelezionaDoppioni()
Static NumLast As Long
Dim i As Long, j As Long, k As Long, ColorCode As Long
Dim Ra1 As Range, NumCell As Long
Dim DoubletonFound As Boolean, SkipBlank As Boolean

' Definizioni
' ---------------------------
ColorCode = 35 ' 13434828
Set Ra1 = [Sheet2!A5359]
' ---------------------------
NumCell = Ra1.Count
Ra1.Interior.ColorIndex = xlNone
SkipBlank = True
Select Case NumLast
Case Is = 0
NumLast = 1
Case Is = NumCell
NumLast = 1
Exit Sub
End Select
For i = NumLast To NumCell - 1
If IsEmpty(Ra1.Item(i)) And SkipBlank Then GoTo Continue
For j = i + 1 To NumCell
For k = 1 To NumLast - 1
If Ra1.Item(i) = Ra1.Item(k) Then
GoTo Continue
End If
Next
If Ra1.Item(i) = Ra1.Item(j) Then
Ra1.Item(i).Interior.ColorIndex = ColorCode
Ra1.Item(j).Interior.ColorIndex = ColorCode
'Ra1.Item(j).Select
DoubletonFound = True
End If
Next
If DoubletonFound Then
NumLast = i + 1
Exit Sub
End If
Continue:
Next
If Not DoubletonFound And i = NumCell Then
NumLast = 1
End If
End Sub

Bruno

 

[Erny]

Thanks for this additional explanation, Paul. Indeed I misunderstood your
request. In your case I would use the following formula in the conditional
formatting:

=SUM(IF($A$1:$A$1600=$A1,IF($B$1:$B$1600=$B1,1,0)))>1

Note that the above example is just for the case for example if column A
contains the names in the 1600 first rows and column B the line #. Change
the exact values as required.
Note also that if you did this in an Excel cell it would expect it as an
array formula, but under conditional formatting it should work as is.

Kind regards,
Erny