# 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  Reply With Quote

2. Put below formula in Conditional Formatting.

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

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" ?  Reply With Quote

if and formula 