• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Highlight Duplicates by two column Values

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.

79612
 
@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?

80182
 

Attachments

  • Find Duplicates in multiple columns. .xlsx
    9.2 KB · Views: 2
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
 
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

  • Chandoo48269Find Duplicates in multiple columns.xlsx
    11.6 KB · Views: 2
Last edited:
Back
Top