remove duplicates from multiple columns

[bigdan]

I have some duplicate data which I need to highlight so I can delete. What complicates this is that to determine what's a duplicate I need to look at multiple columns.

The data is in columns A and B, and I want to remove the duplicate data from col B. So normally I'd just set a conditional format on col B and then delete the duplicates. But in this case col B is just dollar values, so a lot of valid data would show up as a duplicate. I only want to delete data in col B IF the corresponding cell in col A is also a duplicate.

How would I do that?

 

[kpasa]

I only want to delete data in col B IF the corresponding cell in col A is also a duplicate.

Here's a quick example of how you could accomplish this:

Sub dupremove()    
     For Each cell In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If InStr(0, cell.Value, cell.Offset(0, 1).Value) = 0 Then
            'values are not duplicate, do nothing
        Else
            'code to remove the duplicate, such as:
            cell.Offset(0, 1).Value = ""
        End If
    Next cell
End Sub

 

[bigdan]

thanks a lot!

unfortunately i cant use VBA at work. I should've mentioned that, sorry.

Is there another way?

Here's a quick example of how you could accomplish this:

Sub dupremove()    
     For Each cell In Range("A2:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If InStr(0, cell.Value, cell.Offset(0, 1).Value) = 0 Then
            'values are not duplicate, do nothing
        Else
            'code to remove the duplicate, such as:
            cell.Offset(0, 1).Value = ""
        End If
    Next cell
End Sub

 

[kpasa]

thanks a lot!

unfortunately i cant use VBA at work. I should've mentioned that, sorry.

Is there another way?

No, without VBA, you can only write formulas or use conditions to indicate which cells should be deleted. Formulas cannot change another cell's value based on a condition.

 

[kpasa]

The only alternative workaround is to write your own addin that you load into your work instance of excel. But that really depends on your work/group policies. They may block that as well.