Find matching rows and blank cells with duplicate values

Question

I can't seem to locate a solution to a common scenario I face using Excel. My VBA ability is limited, so I'm not sure how to proceed with coding, but here is what I am trying to solve.

I have a spreadsheet listing rows that will frequently have additional rows mostly matching previous rows. For example, there may be two rows for the same customer, where the data is the same except that the second row has a different phone number. Everything else is the same, including a unique ID in column A. What I am looking to do is create code that will find a matching second (or additional) rows and blank any fields on the subsequent rows that match the previous, leaving any fields with different values alone.

This site won't let me insert a diagram showing what I'm talking about, but an example would be a spreadsheet with three columns- ID, Name, Phone. ID number 99 has two entries with the same ID and name, but different phone. I'd like to be able to blank the fields where the ID and name are duplicated and leave only the phone.

In this example, it's phone number, but it could be any column or combination of columns.

Is there a viable way to do this?

are there any instances where the information appears in the wrong column? And do you have column headers? All on one sheet? — peege
Yes, the information would always be in the correct column. If there is no information for that particular column it would just be blank. There are column headers and it would always be on one sheet. — Beeker318

Raugmor Raugmor · Accepted Answer · 2014-12-08T18:14:22

below has been tested:

Sub test()

For Each cell In Range("A3:C1000")

  If cell.Value = cell.Offset(-1, 0).Value Then cell.ClearContents

Next

End Sub

just change Range to whichever Range you require, skipping first row(i skipped row 2) as it an anchor which is unique anyway. P.S. this only works sequentially though so you need a sorted list

EDIT

first solution clearly was wrong, apologies, please try below, please note there is one caveat in it: if you have an entry in any particular column which repeats against some other entry which in fact is different - let say same phone number for a different customer, it will ALSO delete that entry. it checks each entry against all previous entries in the same column, if it appeared previously, it will delete it.

Sub test()
Dim cell As Range
Dim c As Range

For Each cell In Range("A3:C1000")

  On Error Resume Next
  Set c = Range(Cells(2, cell.Column), Cells(cell.Row - 1, cell.Column)).Find(cell.Value)
  On Error GoTo 0
  If Not c Is Nothing Then

    cell.ClearContents

  End If

Next

End Sub

Find matching rows and blank cells with duplicate values

1 Answers