0

I am comparing 2 columns in WPS spreadsheet using the 'Highlight Duplicates' option. Once it is highlighted, if I use remove duplicates it isn't deleting the duplicates. If i manually delete the cells, the highlighted cells, will be de-higlighted.

I want to check for duplicates in these two columns and deleted the duplicated cells, so I can take the remaining cells and remove the required data.

Image containing the data 1: https://i.sstatic.net/SVoGv.jpg

I tried using the result in this link but it doesn't work for me. (1) Is that if there is a function that can check for duplicates in both the columns. (It has to use the value in Column A and check for it completely in Column B and highlight the cell with which it matches.)

fixer1234
  • 28,064
BRMBU
  • 1
  • 2
  • 7

1 Answers1

0

This method will help you to Delete Duplicate Values from multiple Cells:

enter image description here

Step 1:

enter image description here

How it works:

  • Select entire data range & Apply Conditional Format, write this Formula for New Rule.

    =COUNTIF($A2:$C2,A2)>1

  • Apply an appropriate Format & finish with Ok.

Step 2:

  • Copy & Paste this VBA(Macro) Code with the Sheet as Standard Module.

    Sub DeleteCells()

Range("A2:C4").Select
For Each Cells In Selection

    If Cells.Interior.Color = Excel.XlRgbColor.rgbYellow Then

Cells.ClearContents
Cells.Interior.Color = xlNone

    End If
Next

End Sub

  • RUN the Macro.

enter image description here

N.B.

  • Adjust cell references in the Formula as needed.
  • In VBA code rgbYellow is editable.
Rajesh Sinha
  • 9,403