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

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!
=====================================================

"psilzle" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
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

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" <(E-Mail Removed)> schrieb im Newsbeitrag
news:(E-Mail Removed)...
>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,

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


On May 31, 1:08 pm, "Erny" <Erny-removehyphensandthistext-
Me...@hotmail.com> wrote:
> 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" <psil...@bbei.com> schrieb im Newsbeitragnews:(E-Mail Removed)...
>
>
>
> >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- Hide quoted text -
>
> - Show quoted text -

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" <(E-Mail Removed)> schrieb im Newsbeitrag
news:(E-Mail Removed)...
> 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
>
>
> On May 31, 1:08 pm, "Erny" <Erny-removehyphensandthistext-
> Me...@hotmail.com> wrote:
>> 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" <psil...@bbei.com> schrieb im
>> Newsbeitragnews:(E-Mail Removed)...
>>
>>
>>
>> >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- Hide quoted text -
>>
>> - Show quoted text -
>
>

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

On May 31, 2:20 pm, "Erny" <Erny-removehyphensandthistext-
Me...@hotmail.com> wrote:
> 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" <psil...@bbei.com> schrieb im Newsbeitragnews:(E-Mail Removed)...
>
>
>
> > 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
>
> > On May 31, 1:08 pm, "Erny" <Erny-removehyphensandthistext-
> > M...@hotmail.com> wrote:
> >> 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" <psil...@bbei.com> schrieb im
> >> Newsbeitragnews:(E-Mail Removed)...
>
> >> >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- Hide quoted text -
>
> >> - Show quoted text -- Hide quoted text -
>
> - Show quoted text -

"psilzle" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

>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

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

Thanks!

Paul

On Jun 1, 10:10 am, "Bruno Campanini" <B...@gmail.com> wrote:
> "psilzle" <psil...@bbei.com> wrote in message
>
> news:(E-Mail Removed)...
>
> >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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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

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


"psilzle" <(E-Mail Removed)> schrieb im Newsbeitrag
news:(E-Mail Removed)...
>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
>
> On May 31, 2:20 pm, "Erny" <Erny-removehyphensandthistext-
> Me...@hotmail.com> wrote:
>> 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" <psil...@bbei.com> schrieb im
>> Newsbeitragnews:(E-Mail Removed)...
>>
>>
>>
>> > 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
>>
>> > On May 31, 1:08 pm, "Erny" <Erny-removehyphensandthistext-
>> > M...@hotmail.com> wrote:
>> >> 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" <psil...@bbei.com> schrieb im
>> >> Newsbeitragnews:(E-Mail Removed)...
>>
>> >> >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- Hide quoted text -
>>
>> >> - Show quoted text -- Hide quoted text -
>>
>> - Show quoted text -
>
>