Replace Duplicates With Whitespace

I have a spreadsheet that is generated by vbscript that the first column looks like the text below.

Mechanics
Maint Supplies & Equip
Maint Supplies & Equip
Repairs to Paving
Repairs to Paving
Repairs to Roof
Repairs to Roof
Repairs to HVAC
Repairs to HVAC
Repairs to HVAC

I would like to have it look like:

Mechanics
Maint Supplies & Equip

Repairs to Paving

Repairs to Roof

Repairs to HVAC

Putting blank cells in the place of the duplicated cells. I figured out how to delete the dups but that will not work for my purpose.....Any ideas?

Sub UniqueSelection()
    Dim coll As New Collection
    Dim lcount As Long
    Dim cell As Range
     
    On Error Resume Next
    For Each cell In Selection
        coll.Add cell.Value, CStr(cell.Value)
      Next cell
    
     'This kills all dups but removes the cells as well
    Selection.ClearContents
    
    'This does somthing that I really don't understand
'Selection.Replace What:=cell.Value, Replacement:=Null, LookAt _
':=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
'ReplaceFormat:=False

    
   On Error GoTo 0
     
   lcount = 0
   For Each cell In Selection
        lcount = lcount + 1
        If lcount > coll.Count Then Exit For
        cell.Value = coll.Item(lcount)
    Next cell
   
   
End Sub

Hi

Assuming that your raw data is in the range A1:A10 then try

Sub ccc()

  For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If WorksheetFunction.CountIf(Range("A1:A" & i), Range("A" & i).Value) > 1 Then Cells(i, "A").ClearContents
  Next i
End Sub

rylo

That works much better than what I was trying, Thanks so much.

One more question, how can I run that code against two different columns, A & B?

Hi

Do you mean that if you clear an entry from column A, then the entry in column B for the same row should be cleared, or do you want to perform a completely separate test on the data in column B???

rylo

Sorry for not being clear, actually either of those would work because if the data is duplicated in a1, a2 then a seperate value is duplicated in b1, b2...