# Thread: IF AND formula (I think?)

1. ## IF AND formula (I think?)

I’m somewhat new to Excel and have been trying to figure out a formula for the past 5 hours. I need to know the formula for finding combinations of duplicate data in ROWS only, not columns. For example, if I have 3 rows and 3 columns of data how can I search for duplicate data by rows. I’ve made a simple example below.

Row 1: 2a 5c yy
Row 2: 9a 2a 6n
Row 3: 5c 2a ts

In this example, if I needed to know how many times the combination of 2a and 5c appeared, the formula would show 2 times (rows 1 and 3).
I must admit that I got confused when referencing the Microsoft and other websites that gave examples. None of them worked by finding the data by rows.

Any help would be greatly appreciated!!!!! I'm using Excel 2010

2. Put below formula in Conditional Formatting.

=OR(IF(AND(\$A1="2a",\$B1="5c"),1,IF(AND(\$A1="5c",\$B1="2a"),1,0)))

3. Originally Posted by sambit
Put below formula in Conditional Formatting.

=OR(IF(AND(\$A1="2a",\$B1="5c"),1,IF(AND(\$A1="5c",\$B1="2a"),1,0)))
What if one or both items in the pair are beyond column B ?

Monday
Can you say how many columns you need to search, and whether or not you mean any duplicates or just "5c" and "2a" ?