CODEWITHSUNDEEP
google-sheetsconditional-formatting
Code Guy
Code Guy

Reputation: 3198

Condtional formatting to highlight duplicates fetching across multiple columns

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)

Upvotes: 1

Views: 60

Answers (1)

General Grievance
General Grievance

Reputation: 5033

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.

Upvotes: 2

Related Questions