Highlighting duplicate information within excel files

[was0696]

I need to compare two columns of data within an excel file. In the first column I'm looking for duplicate numbers within the column, I then need to check if any in the rows of these duplicate items data within the second column is also duplicated in the the next column of these rows. If so I want to in some matter highlight whenever this is the case. How can I do this automatically?

To make it clearer please see below: I would only want rows index number 2 and 5 highlighted in this example. As both data in column A and B are duplicates.

Index A B
1 123 A
2 125 A
3 123 B
4 134 B
5 125 A
6 134 C
7 123 D


Hope that makes sense. Can anyone suggest a solution?

[martindwilson]

with data in a1:b7
select range a1:b7 the use conditional format
=SUMPRODUCT(($A$1:$A$7=$A1)*($B$1:$B$7=$B1))>1

[Domski]

See attached...

Dom

[veejar]

Please see the attached file

Regards,

Veejar

[was0696]

Sorry I didn't make myself clear the example I posted with some numbers and letters only simplified expression of problem to try and explain it in actual fact more along the lines of that attached.

Several files of 1000s of items with different serial number lengths (some blank) and with a part number (of different lengths and sometimes blank) that have to seprate out from information infront of it.

So I want to pick out rows where both columns are duplicates, such as 11 and 17. Ignoring rows where one column is blank.

[was0696]

Have tried with the following formulas in two new columns:

=IF(A6="","",CONCATENATE(RIGHT(B6,12),A6))

and

=IF(COUNTIF(C:C,C6)>100000,"",COUNTIF(C:C,C6))

And then filtered out the blanks and 1s from the final column.

But really I’m looking for something that will not only highlight which rows are duplicates, but also match them up together. To save me searching through all the duplicate rows.

[veejar]

Hi WAS0696,

Please see my previous post.

Regards,

Veejar

[martindwilson]

=SUMPRODUCT(($A$6:$A$57=$A6)*($A$6:$A$57<>"")*($B$6:$B$57=$B6)*($B$6:$B$57<>""))>1
adjust range to suit