duplicated cells in a column

[lezawang]

Hi
Can I just delete duplicated cells in a column BUT without shifting other cells to the top? I used Data-->remove duplicate but that would remove duplicated cells and move everything up.
Thanks

 

[Legacy 143009]

Hi
Can I just delete duplicated cells in a column BUT without shifting other cells to the top? I used Data-->remove duplicate but that would remove duplicated cells and move everything up.
Thanks

You can achieve this only with vba. Another solution is make a helper column. For example you have your data in column A. In B2 write:
=IF(MATCH($A2,$A$1:$A1,0)>0,"",$A2)

Then pull down. After copy Colum B and paste as only values to column A.

 

[Peter_SSs]

@ Flashbond
Your formula needs a tweak as it returns blank or #N/A

@ lezawang
Another manual way
1. Select the column by clicking its heading label
2. Home -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values -> OK
3. With the column still selected -> Data -> Filter
4. Note the cell colour of the duplicates (probably the default pink colour) & click the filter drop-down in the top cell and choose -> Filter by Color -> Select the appropriate cell color for the duplicates
5. Select from row 2 to the bottom of the visible cells & hit the Delete key
6. Remove the AutoFilter altogether or from the drop-down choose Select All

 

[Legacy 143009]

@ Flashbond
Your formula needs a tweak as it returns blank or #N/A

@ lezawang
Another manual way
1. Select the column by clicking its heading label
2. Home -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values -> OK
3. With the column still selected -> Data -> Filter
4. Note the cell colour of the duplicates (probably the default pink colour) & click the filter drop-down in the top cell and choose -> Filter by Color -> Select the appropriate cell color for the duplicates
5. Select from row 2 to the bottom of the visible cells & hit the Delete key
6. Remove the AutoFilter altogether or from the drop-down choose Select All

Yeah, it was not wise to use MATCH. COUNTIF will work:
=IF(COUNTIF($A$1:$A1,$A2)>0,"",$A2)

 

[lezawang]

That is really good. However, when I delete, excel will delete all duplicated values. What I want.. lets say I have 3 cells with value of =10, A1, A5, A20 = 10
I want excel to delete A5, and A20 and keep A10. In your example, it will delete 3 of them A1 A5 and A20

@ Flashbond
Your formula needs a tweak as it returns blank or #N/A

@ lezawang
Another manual way
1. Select the column by clicking its heading label
2. Home -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values -> OK
3. With the column still selected -> Data -> Filter
4. Note the cell colour of the duplicates (probably the default pink colour) & click the filter drop-down in the top cell and choose -> Filter by Color -> Select the appropriate cell color for the duplicates
5. Select from row 2 to the bottom of the visible cells & hit the Delete key
6. Remove the AutoFilter altogether or from the drop-down choose Select All

 

[Peter_SSs]

In your example, it will delete 3 of them A1 A5 and A20

That is correct and it was because in your original post you said remove duplicates could be used except that it moved all the cells up. Remove duplicates also removes all the duplicated values and you didn't mention that was a problem.

I don't have time right now but if a suitable solution doesn't appear, I will have another look when I can.

 

[Fluff]

How about

Sub ClearDupes()

   Dim Cl As Range
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A1", Range("A" & Rows.Count).End(xlUp))
         If Not .exists(Cl.Value) Then
            .Add Cl.Value, Nothing
         Else
            Cl.ClearContents
         End If
      Next Cl
   End With
End Sub

 

[Peter_SSs]

A couple more options. I'v assumed the column is column A & is not populated with formulas or likely the duplication problem could be eliminated by those formulas.

1. Use the manual method suggested by Flashbond in post #2 , but with this modified formula in B2, copied down
=IF(A2="","",IF(COUNTIF($A$1:$A1,$A2)>0,"",$A2))

2. Another macro option.

Sub Clear_Dupes()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace(Replace("if(#="""","""",if(match(#,#,0)=row(#)-row(^)+1,#,""""))", "#", .Address), "^", .Cells(1).Address))
  End With
End Sub