HOW TO LOCATE DUPLICATES ON TWO COLUMNS

[HERNAN]

I have this:
A B C
1 189 xxx $1.5
2 001 xxx $8.0
3 189 xxx $1.5
4 189 xxx $2.0

I just want to know when only column A and column C have the same numbers,
not just one column;... but both have to match. On this case would be Row # 3
the one that is duplicate and I wanted to find it.
Thank you so much!!!!!

[jetted]

hi

Would this be acceptable (put this in a module and run macro "main")
Sub main()
'Concatenate col a and col c
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
For i = 1 To rowcount
Range("a" & i).Select
val1 = ActiveCell.Value
Range("c" & i).Select
val2 = ActiveCell.Value
Range("d" & i).Select
ActiveCell.Value = val1 & val2
Next
Call sort_cold
Call hightligh_duplicate
Call clean_up
End Sub
Sub sort_cold()
rowcount = Cells(Cells.Rows.Count, "a").End(xlUp).Row
Range("a1:" & "d" & rowcount).Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("a1").Select
End Sub
Sub hightligh_duplicate()
Range("d1").Select
FirstItem = ActiveCell.Value
seconditem = ActiveCell.Offset(1, 0).Value
offsetcount = 1
Do While ActiveCell <> ""
If FirstItem = seconditem Then
ActiveCell.Offset(0, -3).Select
ActiveCell.Offset(offsetcount, 0).Interior.Color = RGB(255, 0, 0)
ActiveCell.Offset(0, 3).Select
offsetcount = offsetcount + 1
seconditem = ActiveCell.Offset(offsetcount, 0).Value
Else
ActiveCell.Offset(offsetcount, 0).Select
FirstItem = ActiveCell.Value
seconditem = ActiveCell.Offset(1, 0).Value
offsetcount = 1
End If
Loop
ScreenUpdating = True
End Sub

Sub clean_up()
Columns("D:D").Select
Selection.ClearContents
Range("A1").Select
End Sub

[SimonCC]

In cell D1 put in the formula:
="A"&A1&"C"&C1
Copy the formula down as far as you have data.

Then with column D selected, go to Format | Conditional Formatting.
Select Formula Is for the dropdown box.
Put =COUNTIF(D:D,D1)>1 in the formula box.
Click Format button and pick a color in the Patterns tab.

All the duplicte ones should then be highlighted in column D.

-Simon

"HERNAN" wrote:

> I have this:
> A B C
> 1 189 xxx $1.5
> 2 001 xxx $8.0
> 3 189 xxx $1.5
> 4 189 xxx $2.0
>
> I just want to know when only column A and column C have the same numbers,
> not just one column;... but both have to match. On this case would be Row # 3
> the one that is duplicate and I wanted to find it.
> Thank you so much!!!!!

[HERNAN]

Thank you so much,... but is not working I did it exactly as you told me to.

"SimonCC" wrote:

> In cell D1 put in the formula:
> ="A"&A1&"C"&C1
> Copy the formula down as far as you have data.
>
> Then with column D selected, go to Format | Conditional Formatting.
> Select Formula Is for the dropdown box.
> Put =COUNTIF(D:D,D1)>1 in the formula box.
> Click Format button and pick a color in the Patterns tab.
>
> All the duplicte ones should then be highlighted in column D.
>
> -Simon
>
> "HERNAN" wrote:
>
> > I have this:
> > A B C
> > 1 189 xxx $1.5
> > 2 001 xxx $8.0
> > 3 189 xxx $1.5
> > 4 189 xxx $2.0
> >
> > I just want to know when only column A and column C have the same numbers,
> > not just one column;... but both have to match. On this case would be Row # 3
> > the one that is duplicate and I wanted to find it.
> > Thank you so much!!!!!

[SimonCC]

Hmm, not really sure what's wrong. Do you get any error messages? Or maybe
no messages at all but just no highlights in column D? Can you post a few
sample resulting values for column D? Also try opening the condition format
pop up again while you're in one of the cells in column D, what do you see?
That's all the questions I can think of for now.

-Simon

"HERNAN" wrote:

> Thank you so much,... but is not working I did it exactly as you told me to.
>
> "SimonCC" wrote:
>
> > In cell D1 put in the formula:
> > ="A"&A1&"C"&C1
> > Copy the formula down as far as you have data.
> >
> > Then with column D selected, go to Format | Conditional Formatting.
> > Select Formula Is for the dropdown box.
> > Put =COUNTIF(D:D,D1)>1 in the formula box.
> > Click Format button and pick a color in the Patterns tab.
> >
> > All the duplicte ones should then be highlighted in column D.
> >
> > -Simon
> >
> > "HERNAN" wrote:
> >
> > > I have this:
> > > A B C
> > > 1 189 xxx $1.5
> > > 2 001 xxx $8.0
> > > 3 189 xxx $1.5
> > > 4 189 xxx $2.0
> > >
> > > I just want to know when only column A and column C have the same numbers,
> > > not just one column;... but both have to match. On this case would be Row # 3
> > > the one that is duplicate and I wanted to find it.
> > > Thank you so much!!!!!

[Dav]

is the conditional format exactly as simon said, ii has not put " " around the formula? you have picked a colour to highlight as well!

Technically what u asked for is in cell d1

Put =COUNTIF($D$1:D1,D1)>1 in the formula box. as you said you only wanted the second case highlighted. the first way will highlight both.

you then need to copy the format down to the other cells. paste special format

Regards

Dav

[HERNAN]

just nothing happens, no messages, do I have to copy and paste that formula
just in D1?

"SimonCC" wrote:

> Hmm, not really sure what's wrong. Do you get any error messages? Or maybe
> no messages at all but just no highlights in column D? Can you post a few
> sample resulting values for column D? Also try opening the condition format
> pop up again while you're in one of the cells in column D, what do you see?
> That's all the questions I can think of for now.
>
> -Simon
>
> "HERNAN" wrote:
>
> > Thank you so much,... but is not working I did it exactly as you told me to.
> >
> > "SimonCC" wrote:
> >
> > > In cell D1 put in the formula:
> > > ="A"&A1&"C"&C1
> > > Copy the formula down as far as you have data.
> > >
> > > Then with column D selected, go to Format | Conditional Formatting.
> > > Select Formula Is for the dropdown box.
> > > Put =COUNTIF(D:D,D1)>1 in the formula box.
> > > Click Format button and pick a color in the Patterns tab.
> > >
> > > All the duplicte ones should then be highlighted in column D.
> > >
> > > -Simon
> > >
> > > "HERNAN" wrote:
> > >
> > > > I have this:
> > > > A B C
> > > > 1 189 xxx $1.5
> > > > 2 001 xxx $8.0
> > > > 3 189 xxx $1.5
> > > > 4 189 xxx $2.0
> > > >
> > > > I just want to know when only column A and column C have the same numbers,
> > > > not just one column;... but both have to match. On this case would be Row # 3
> > > > the one that is duplicate and I wanted to find it.
> > > > Thank you so much!!!!!

[Dav]

i told you to copy it down, simon told you to select the column to start with, both have a similar effect. go to a cell where it is not working and check the formats, conditional formating, are there any there?

Regards

Dav