Deleting all duplicates but one in a single column

[JamieDuncan]

How would I go about Deleting all duplicates in a column except for the first entry of each value in that column, so that

A
A
A
A
A
B
B
B
B
C
C
C

would become

A
.
.
.
.
B
.
.
.
C
.
.

(full stops being blanks)

 

[Domenic]

Assuming that A2:A13 contains the data, try...

B2, copied down:

=IF(ISNA(MATCH(A2,A$1:A1,0)),A2,"")

Hope this helps!

 

[Krishnakumar]

Sub ClearDupes()
With Range("A1", [a65536].End(xlUp))
    .AdvancedFilter Action:=xlFilterInPlace, Unique:=True
    .SpecialCells(xlCellTypeVisible).Select
    ActiveSheet.ShowAllData
    Selection.EntireRow.Hidden = True
    .SpecialCells(xlCellTypeVisible).ClearContents
    .Rows.Hidden = False
End With
End Sub

 

[HalfAce]

I think even easier...
In B1: =A1
In B2 (and copied down): =IF(A2=A1,"",A2)

- If you actually want the dots in the matching cells as in your posted example then
in B2 & down: =IF(A2=A1,".",A2)

 

[Stormseed]

How would I go about Deleting all duplicates in a column except for the first entry of each value in that column, so that

A
A
A
A
A
B
B
B
B
C
C
C

would become

A
.
.
.
.
B
.
.
.
C
.
.

(full stops being blanks)

Well...I aint an expert building Macros alike these MVPs but I can provide help in terms of a formula according to my caliber. U need to manually collate the data which would be very simple !

Assume ur data starts from A1

Use: if(countif(Range, A1)>1,"True", "False")

this will return "True" when a duplicate else will return false. Copy the data till last row and paste the same as values...Sort the data according to True and False !

 

[JamieDuncan]

Thanks for your help all, should have mentioned I needed it to be in macro form, for an automated process.

Thanks Kris your code works perfectly