Check if two rows are the EXACT SAME in MS Excel [closed]

Question

In MS Excel, what is the best way to see if two rows are the exact same? I've tried with conditional formatting but it only seems like I can only check for duplicat CELLS and not whole rows. Thanks in advance for the help!

Answer 1

You need an array formula that compares the 2 rows.

If you want to find out if rows 1 and 2 are exactly the same, enter this formula in a cell that isn't in row 1 or 2:

=AND(EXACT(1:1,2:2))

Instead of pressing Enter after typing the formula, press Ctrl + Shift + Enter, which will tell Excel you want an Array formula.

The result will be TRUE if they match and FALSE if they don't. You'll see curly braces around your formula if you've correctly entered it as an array formula.

Note that the EXACT formula will perform a case-sensitive comparison. Omit it if you want a case-insensitive comparison:

=AND(1:1=2:2)

entered with Ctrl + Shift + Enter as well.

One last thing: if you want to check part of a row instead of the whole row, simply specify that in the formula.

=AND(EXACT(A1:E1,A2:E2))

entered with Ctrl + Shift + Enter, of course.

Answer 2

you can use conditional formatting using a formula like

=or(and($A2:$E2=$A1:$E1),and($A2:$E2=$A3:$E3))

applied to row 2 and below. change the columns to suit