Delete in row C if it is repeated in row D

C

calibansfolly

All my searches have lead me to ways to eliminate duplicates in a
column- not what I'm trying to do... If the value in column C is the
same as the value in column D for the same row, I want to erase it. Is
there an easy way to do this? (I think I remember reading about a way
with conditional formatting once.)
 
R

roadkill

If you just want to hide duplicate values you can use conditional formatting
on values in column C and make the font color white if the value in C matches
the value in D (in conditional format box use "Cell Value is", "equal to",
"=D1" for cell C1 and use the format paintbrush to do the rest of column C).

If you want to actually remove the value from column C you could create a
3rd column (say E), use an "If" statement to compare C and D and then
copy/paste special/value column E back on to column C. The "If" statement in
C1 would look something like "=if(c1=d1,"",c1)"
Will
 
S

squenson via OfficeKB.com

There is no possibility to erase the content of a cell with a formula or a
conditional formatting, only a macro can do it. However, you can hide the
content of a cell with conditional formatting, by using white font on white
background.

Select column C, then click on the menu Format > Conditional Formatting, then
make sure you have
"Cell Value Is", "Equal To", "=$D1" (without quotes), then click on the
button Format and select white color font.

If you want to erase the content of the cells, then this macro should do it:

Sub DeleteSameValue()

Dim i As Long

For i = 1 To 65536
If Cells(i, "C") = Cells(i, "D") And Cells(i, "C") <> "" Then
Cells(i, "C") = ""
End If
Next i

End Sub
 
Top