Conditional Formatting or COUNTIFS formula for duplicate rows?

[AG07509]

Hello,

I have a spreadsheet with 5,000 plus rows and many of the rows containduplicate data. What I am trying to do is if Column A has a duplicate thencompare the data in those rows and if the entire row is a duplicate to somehowidentify them from conditional formating or a formula like COUNTIFS.
Example: A3 and A4 are duplicates so I need to check tosee the remaining part of these two rows are identical.
Is this possible?

<tbody> [TD="width: 25, bgcolor: transparent"][/TD]
[TD="width: 56, bgcolor: transparent"] A [/TD]
[TD="width: 70, bgcolor: transparent"] B [/TD]
[TD="width: 72, bgcolor: transparent"] C [/TD]
[TD="width: 66, bgcolor: transparent"] D [/TD]
[TD="width: 54, bgcolor: transparent"] E [/TD]
[TD="width: 72, bgcolor: transparent"] H [/TD]
[TD="width: 48, bgcolor: transparent"] I [/TD]
[TD="width: 126, bgcolor: transparent"] J [/TD]
[TD="width: 44, bgcolor: transparent"] K [/TD]
[TD="width: 25, bgcolor: transparent"] 1 [/TD]
[TD="width: 56, bgcolor: transparent"]

Case Number​

[/TD]
[TD="width: 70, bgcolor: transparent"]

Service From Date​

[/TD]
[TD="width: 72, bgcolor: transparent"]

Service To Date​

[/TD]
[TD="width: 66, bgcolor: transparent"]

Year​

[/TD]
[TD="width: 54, bgcolor: transparent"]

REVIEW TYPE​

[/TD]
[TD="width: 72, bgcolor: transparent"]

CASE TYPE​

[/TD]
[TD="width: 48, bgcolor: transparent"]

DXC CODE​

[/TD]
[TD="width: 126, bgcolor: transparent"]

DXC DESC​

[/TD]
[TD="width: 44, bgcolor: transparent"]

PXC CODE​

[/TD]
[TD="width: 25, bgcolor: transparent"] 2 [/TD]
[TD="width: 56, bgcolor: transparent"] 9351528 [/TD]
[TD="width: 70, bgcolor: transparent"] 9/22/2016 [/TD]
[TD="width: 72, bgcolor: transparent"] 9/22/2016 [/TD]
[TD="width: 66, bgcolor: transparent"] 2016 - Q3 [/TD]
[TD="width: 54, bgcolor: transparent"] Medical [/TD]
[TD="width: 72, bgcolor: transparent"] Outpatient [/TD]
[TD="width: 48, bgcolor: transparent"] I4891 [/TD]
[TD="width: 126, bgcolor: transparent"] Unspec atrial fibrillati [/TD]
[TD="width: 44, bgcolor: transparent"] 93228 [/TD]
[TD="width: 25, bgcolor: transparent"] 3 [/TD]
[TD="width: 56, bgcolor: transparent"] 9351605 [/TD]
[TD="width: 70, bgcolor: transparent"] 10/26/2016 [/TD]
[TD="width: 72, bgcolor: transparent"] 10/26/2016 [/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"] Medical [/TD]
[TD="width: 72, bgcolor: transparent"] Outpatient [/TD]
[TD="width: 48, bgcolor: transparent"] I480 [/TD]
[TD="width: 126, bgcolor: transparent"] Paroxysmal atrial fibril [/TD]
[TD="width: 44, bgcolor: transparent"] 33282 [/TD]
[TD="width: 25, bgcolor: transparent"] 4 [/TD]
[TD="width: 56, bgcolor: transparent"] 9351605 [/TD]
[TD="width: 70, bgcolor: transparent"] 10/26/2016 [/TD]
[TD="width: 72, bgcolor: transparent"] 10/26/2016 [/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"] Medical [/TD]
[TD="width: 72, bgcolor: transparent"] Outpatient [/TD]
[TD="width: 48, bgcolor: transparent"] I480 [/TD]
[TD="width: 126, bgcolor: transparent"] Paroxysmal atrial fibril [/TD]
[TD="width: 44, bgcolor: transparent"] 33282 [/TD]
[TD="width: 25, bgcolor: transparent"] 5 [/TD]
[TD="width: 56, bgcolor: transparent"] 9352003 [/TD]
[TD="width: 70, bgcolor: transparent"] 1/9/2017 [/TD]
[TD="width: 72, bgcolor: transparent"] 1/10/2017 [/TD]
[TD="width: 66, bgcolor: transparent"] 2017 - Q1 [/TD]
[TD="width: 54, bgcolor: transparent"] NULL [/TD]
[TD="width: 72, bgcolor: transparent"] Outpatient [/TD]
[TD="width: 48, bgcolor: transparent"] R008 [/TD]
[TD="width: 126, bgcolor: transparent"] Other abnormalities of h [/TD]
[TD="width: 44, bgcolor: transparent"] 33282 [/TD]
[TD="width: 25, bgcolor: transparent"] 6 [/TD]
[TD="width: 56, bgcolor: transparent"] 9353964 [/TD]
[TD="width: 70, bgcolor: transparent"] 11/7/2016 [/TD]
[TD="width: 72, bgcolor: transparent"] 11/7/2016 [/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"] Medical [/TD]
[TD="width: 72, bgcolor: transparent"] Outpatient [/TD]
[TD="width: 48, bgcolor: transparent"] R002 [/TD]
[TD="width: 126, bgcolor: transparent"] Palpitations [/TD]
[TD="width: 44, bgcolor: transparent"] 93228 [/TD]
[TD="width: 25, bgcolor: transparent"] 7 [/TD]
[TD="width: 56, bgcolor: transparent"] 9353977 [/TD]
[TD="width: 70, bgcolor: transparent"] 11/23/2016 [/TD]
[TD="width: 72, bgcolor: transparent"] 11/23/2016 [/TD]
[TD="width: 66, bgcolor: transparent"][/TD]
[TD="width: 54, bgcolor: transparent"] Medical [/TD]
[TD="width: 72, bgcolor: transparent"] Outpatient [/TD]
[TD="width: 48, bgcolor: transparent"] R002 [/TD]
[TD="width: 126, bgcolor: transparent"] Palpitations [/TD]
[TD="width: 44, bgcolor: transparent"] 93228 [/TD]
[TD="width: 25, bgcolor: transparent"] 8 [/TD]
[TD="width: 56, bgcolor: transparent"] 9358036 [/TD]
[TD="width: 70, bgcolor: transparent"] 2/16/2016 [/TD]
[TD="width: 72, bgcolor: transparent"] 2/16/2016 [/TD]
[TD="width: 66, bgcolor: transparent"] 2016 - Q1 [/TD]
[TD="width: 54, bgcolor: transparent"] Medical [/TD]
[TD="width: 72, bgcolor: transparent"] Outpatient [/TD]
[TD="width: 48, bgcolor: transparent"] R002 [/TD]
[TD="width: 126, bgcolor: transparent"] Palpitations [/TD]
[TD="width: 44, bgcolor: transparent"] 93229 [/TD]
[TD="width: 25, bgcolor: transparent"] 9 [/TD]
[TD="width: 56, bgcolor: transparent"] 9358036 [/TD]
[TD="width: 70, bgcolor: transparent"] 2/16/2016 [/TD]
[TD="width: 72, bgcolor: transparent"] 2/16/2016 [/TD]
[TD="width: 66, bgcolor: transparent"] 2016 - Q1 [/TD]
[TD="width: 54, bgcolor: transparent"] Medical [/TD]
[TD="width: 72, bgcolor: transparent"] Outpatient [/TD]
[TD="width: 48, bgcolor: transparent"] R002 [/TD]
[TD="width: 126, bgcolor: transparent"] Palpitations [/TD]
[TD="width: 44, bgcolor: transparent"] 93229 [/TD]
</tbody>

 

[Joe4]

Welcome to the Board!

Select cells A2:K9, go to Conditional Formatting, and enter this Conditional Formatting formula:

=OR(AND($A2=$A1,$B2=$B1,$C2=$C1,$D2=$D1,$E2=$E1,$F2=$F1,$G2=$G1,$H2=$H1,$I2=$I1,$J2=$J1,$K2=$K1),AND($A2=$A3,$B2=$B3,$C2=$C3,$D2=$D3,$E2=$E3,$F2=$F3,$G2=$G3,$H2=$H3,$I2=$I3,$J2=$J3,$K2=$K3))

and choose the red font color.

 

[AG07509]

Welcome to the Board!

Select cells A2:K9, go to Conditional Formatting, and enter this Conditional Formatting formula:

=OR(AND($A2=$A1,$B2=$B1,$C2=$C1,$D2=$D1,$E2=$E1,$F2=$F1,$G2=$G1,$H2=$H1,$I2=$I1,$J2=$J1,$K2=$K1),AND($A2=$A3,$B2=$B3,$C2=$C3,$D2=$D3,$E2=$E3,$F2=$F3,$G2=$G3,$H2=$H3,$I2=$I3,$J2=$J3,$K2=$K3))

and choose the red font color.

Worked perfectly. Thank you very much.

 

[Joe4]

You are welcome!

It is a bit long, but the logic is quite simple. It is simply checking to see for any row, if all the columns have the same values as the row above or row below.

 

[jtakw]

Hi,

Just another way:

=OR(SUMPRODUCT(--($A2:$K2=$A3:$K3))=11,SUMPRODUCT(--($A2:$K2=$A1:$K1))=11)