Clear cell content if found matching in another column

[maheshr68]

Greetings!!

Need to solve this puzzle, unsuccessfully googled..

Have data in columns A,B and C as -

country,region and city

India, Maharashtra,Thana
India,Madhya Pradesh,Gwalior
United Arab Emirates,Dubai,Dubai
India,India,India
Pakistan,Pakistan,Pakistan
United Arab Emirates,Abu Dhabi,Abu Dhabi
India,Delhi,Delhi
Spain,Madrid,Pozuelo De Alarcón
Spain,Madrid,Madrid
Spain,Madrid,Madrid
Spain,Madrid,Fuenlabrada

assuming the above entries start from row 2, cells to be cleared

a) in colB w.r.to colA
B5
B6
B11

b) in colC w.r.to colB
C4
C5
C6
C7
C8
C10

Unsuccessfully tried vlookup to filter the unwanted entries..

Best Regards,
Mahesh

 

[UniMord]

I don't see a consistent requirement in which cells you wish to clear.

If you want to clear from Column B any values that equal the corresponding cell in Column A, then why do you want to clear B11 (A11 = Spain, B11 = Madrid)?

If you want to clear from Column C any values that equal the corresponding cell in Column B, then why don't you want to clear C11 (B11 = Madrid, C11 = Madrid)?

That said, if I understand you correctly, then the easiest solution is to "clear" them with Conditional Formatting.

Select B2:C12 and in Conditional Formatting/Enter New Rule/Use a Formula, enter =B2=A2

Then, choose Format/Number/Custom/Type and enter ;;;

OK your way out and you should have what you want.
The values are still there, but they'll be invisible. Does that work for you?

If not, you can enter in D2: =IF(B2<>A2, B2, "") and copy it to D2:E12.

You can then hide B:C or Group and close them.

 

[UniMord]

Alternatively, if you want to use a macro to really get rid of the values, then, enter the following code into a module:

Sub clearDupes()
    Application.ScreenUpdating = False
 
    With Selection
        Dim i!, j!
 
        For j = .Columns.Count To 2 Step -1
            For i = 1 To .Rows.Count
                If .Cells(i, j) = .Cells(i, j - 1) Then .Cells(i, j).ClearContents
            Next i
        Next j
    End With
 
    Application.ScreenUpdating = True
End Sub

Then, select the entire table and run the macro.

 

[maheshr68]

Greetings UniMord,

Thank You!!, this macro will help very much solve the puzzle.

Since there was no email notification on this particular thread & tied up with couple of other things too ) , i was totally unaware of someone having posted a valuable reply.

Best Regards,
Mahesh