# Highlight Duplicates by two column Values

#### Anbuselvam K

##### Member
Dear Excel Genius

In the below values, Many Names and F1 to F6 are repeated and duplicates exist. But I want to highlight the duplicates with both the columns, not just in one.

For Example, Anbu F1 is the real duplicates, but if I apply Conditional Formates to find the duplicates, it highlights all the names and F1 to F6 as it is compared values in one column only.

Finally, I want to highlight 1st and 7th-row values as duplicates. Please suggest the way to get the same.

#### Anbuselvam K

##### Member
@pecoflyer

As per your suggested link, I found the duplicate value where the two columns are having data.

But Now the actual data got increased up to 300 columns. For example, I have added some small data like the below picture.

As per the below picture Products, 2 and 10 are duplicates as both the products have the same % value in the RMs.

The below example has only 7 RMs, but the actual sheet is having 300 RMS.

Could you please share some formula on how to highlight the repeated products with the same % values?

#### Attachments

• 9.2 KB Views: 2

#### Anbuselvam K

##### Member
Dear All,

Is there any other shorter formula than the below one? to find the duplicates in more than 300 column values?

=Countifs(E8:E18,E8,F8:F18,F8.........................................KQ8:KQ18,K8)>1

#### p45cal

##### Well-Known Member
Is there any other shorter formula than the below one?
Depending on your version of Excel, in cell A8:
Code:
``=IF(SUM(--(BYROW(E8:K8=\$E\$8:\$K\$18, LAMBDA(a,ISERROR(MATCH(FALSE,a,0))))))>1,"duplicate","")``
copied down.
If you put the LAMBDA formula into a named range (eg. IsDup) it can be:
Code:
``=IF(SUM(--(BYROW(E8:K8=\$E\$8:\$K\$18,IsDup)))>1,"duplicate","")``
I'm newish to these lambda functions so there's probably more elegant ways of using them.
Columns M and N have another idea.
See attached.

Edit post posting:
Just for fun, I added a formula in column P and updated the attachment.
Code:
``=IsDup2(E8:K8=\$E\$8:\$K\$18)``

#### Attachments

• 11.6 KB Views: 2
Last edited:

#### Anbuselvam K

##### Member
Thanks a lot for your formulas @p45cal

it is working excellent.