Condtional formatting to highlight duplicates fetching across multiple columns

Question

I have a sheet where I need to highlight the the second duplicates if they are same across multiple columns.

Sl. No.     Name    Age     Target(Millions)
1           ABC     30      2.3
2           DEF     40      1.3
3           ABC     30      4.3
4           GHI     44      0.3
5           JKL     33      6.3

For example the serial number 3 column Name and Age is to be highlighted because its a duplicate of serial 1 Name and Age

Note that not both rows to be highlighted.

I tried with

=AND(countif($B$2:$C,B2)>1, countif($B$2:$C,C2)>1)

Calculuswhiz Calculuswhiz · Accepted Answer · 2020-09-13T05:56:36

This should do it. I made it scalable for if you want to add more columns:

=ROWS(QUERY(ArrayFormula(TO_TEXT(ARRAY_CONSTRAIN($A:$D, ROW()-1, COUNTA(1:1)))), "select "&JOIN(",", ArrayFormula("Col"&({2,3})))&" where "&JOIN(" and ", ArrayFormula("Col"&({2,3})&"='"&ArrayFormula(HLOOKUP(ArrayFormula(VLOOKUP($A$1,$1:$1,({2,3}),0)),$A:$D,ROW(),0))&"'"))))

Readable:

=ROWS(
    QUERY(
        ArrayFormula(
            TO_TEXT(ARRAY_CONSTRAIN(
                $A:$D,
                ROW()-1,
                COUNTA(1:1)
            ))
        ),
        "select "&
            JOIN(
                ",", 
                ArrayFormula("Col"&({2,3}))
            )&
        " where "&
            JOIN(
                " and ", 
                ArrayFormula(
                    "Col"&
                        ({2,3})&"='"&
                        ArrayFormula(
                            HLOOKUP(
                                ArrayFormula(
                                    VLOOKUP(
                                        $A$1,
                                        $1:$1,
                                        ({2,3}),
                                        0
                                    )
                                ),
                                $A:$D,
                                ROW(),
                                0
                            )
                        )&
                        "'"
                )
            )
    )
)

Highlight cell if the number of rows with matching cells in previous rows is >0. To add more columns, you'll have to add to every instance of the {2,3}'s. For example, if you want to include D, add a 4 to the arrays.

If you expect to change which columns you want a lot, you can create a separate column elsewhere with the column indexes that you want, then use FILTER in place of the array to save typing.

This highlights any duplicate cell after the original as well.

Condtional formatting to highlight duplicates fetching across multiple columns

1 Answers