Conditional Formatting

[cdnqte]

I have a sheet that has multiple lines of data and in many cases there are repeated lines because I am linking headers and lines.

For example:

A B C D E F
Contract Customer Name Contract Date Charge qty Price
RC10111 Jim Smith Contract 01/01/16 FRT 1 50.00
RC10111 Jim Smith Contract 01/01/16 SUB 1 150.00
RC10111 Jim Smith Contract 01/01/16 PRT 1 35.00
RC10980 Pow Wow Contract 12/15/16 SUB 1 350.00
RC10350 Hot Rod Contract 03/01/16 DEL 1 800.00
RC10350 Hot Rod Contract 03/01/16 SUB 1 300.00

I want to conditional format it so that for the rows with duplicate data in A-C the font is white - only the first row should be in the default font for columns a-c then the rest would be white but d-f would remain in the default text

Any help/suggestions would be greatly appreciated!!!

 

[AliGW]

Select A3 (the SECOND item in the list, not the first) and apply this in the CF formula box:

=$A4=$A3

Set the text formatting to white.

Apply the CF rule to the array $A$3:$C$500 - adjust the size of the array to suit.

 

[Eric W]

Select the range A2:C999 (or whatever the bottom of your range is). Click Conditional Formatting > New Rule > Use a formula > and enter:

=AND($A2=$A1,$B2=$B1,$C2=$C1,$A2<>"")

Click Format... and select your font color. If you just want those cells to appear empty, your white font could work, or you could create a custom format of ;;; which will also hide anything in the cell, and you don't need to worry about background color.

Hope this helps.

 

[Snakehips]

cdnqte,

With A2:C?? selected try this Custom Formula.......

=AND($A2=$A1,$B2=$B1,$C2=$C1)

Hope that helps.

 

[sunny102]

The above solution works if data is sorted, try the formula below in either case, data sorted or not

=AND(COUNTIF($A$4:A4,A4)>1,COUNTIF($B$4:B4,B4)>1,COUNTIF($C$4:C4,C4))

 

[AliGW]

The solutions offered so far work whatever order the data is in. Where did you get the idea that they won't? Are you sure you have understood the request?

 

[GAdamWTW]

If you're sorted alphabetically in one of those first columns you could do an =IF(A2=A1,"dupe","") in the last column and set your conditional format if the last column<>"" then (condition)

 

[sunny102]

Just copy this row to 7th row and see if it works

RC10111 Jim Smith Contract 01/01/16 PRT 1 35.00

Select A3 (the SECOND item in the list, not the first) and apply this in the CF formula box:

=$A4=$A3

Set the text formatting to white.

Apply the CF rule to the array $A$3:$C$500 - adjust the size of the array to suit.

 

[sunny102]

You are just checking consecutive rows, what if same data is in 3rd row and then in 10th row?

The solutions offered so far work whatever order the data is in. Where did you get the idea that they won't? Are you sure you have understood the request?

 

[AliGW]

Yes, because that is what I understand to be the requirement: where there are groups of the same ID together, you only need to see the first line of repeated data. We'll have to wait for the OP to return to find out which of us has interpreted this correctly.