Removing duplicates but leaving data on following rows

[webels]

Hi
I am hoping someone might have a VBA solution for this

I have the following list in excel
12345 14/07/2009
12345 15/07/2009
12345 28/07/2009
35687 17/07/2009
35687 18/07/2009
35687 19/07/2009
23658 20/07/2009
23658 21/07/2009


The first Column is Col A and the second i Col B. I would like to
remove duplicates from Column A to be left with the following

12345 14/07/2009
15/07/2009
28/07/2009
35687 17/07/2009
18/07/2009
19/07/2009
23658 20/07/2009
21/07/2009

Thereby keeping the first incidence of the number. Column B is not
always dates it may sometimes be Surnames.

I would be grateful for any help on this.

Eddie

 

[Don Guillett Excel MVP]

Hi
I am hoping someone might have a VBA solution for this

I have the following list in excel
12345   14/07/2009
12345   15/07/2009
12345   28/07/2009
35687   17/07/2009
35687   18/07/2009
35687   19/07/2009
23658   20/07/2009
23658   21/07/2009

The first Column is Col A and the second i Col B. I would like to
remove duplicates from Column A to be left with the following

12345   14/07/2009
        15/07/2009
        28/07/2009
35687   17/07/2009
        18/07/2009
        19/07/2009
23658   20/07/2009
        21/07/2009

Thereby keeping the first incidence of the number. Column B is not
always dates it may sometimes be Surnames.

I would be grateful for any help on this.

Eddie

Option Explicit
Sub duplicatesdelete()
Dim i As Long
For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1
If Cells(i, "a") = Cells(i - 1, "a") Then Cells(i, "a") = ""
Next i
End Sub

 

[webels]

Option Explicit
Sub duplicatesdelete()
Dim i As Long
For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -1
If Cells(i, "a") = Cells(i - 1, "a") Then Cells(i, "a") = ""
Next i
End Sub- Hide quoted text -

- Show quoted text -

Thank you Don,
This is perfect.

Eddie

 

[Dave Peterson]

I wouldn't do this.

It may screw up any sorts/filters/charts that you decide you want to use in the
future.

Instead, I'd leave the duplicated values, but hide them using conditional
formatting.

Debra Dalgleish show how:
http://www.contextures.com/xlCondFormat03.html#Duplicate

 

[Dave Peterson]

I never like deleting data <bg>. It's usually too difficult to get back if/when
you need it.

But if the original poster wants to fill those cells with the values from above
(for any reason), Debra Dalgleish shares how to do that, too:

http://contextures.com/xlDataEntry02.html
and here (as a video):
http://www.contextures.com/xlVideos01.html#FillBlanks

 

[webels]

I never like deleting data <bg>.  It's usually too difficult to get back if/when
you need it.

But if the original poster wants to fill those cells with the values fromabove
(for any reason), Debra Dalgleish shares how to do that, too:

http://contextures.com/xlDataEntry02.html
and here (as a video):http://www.contextures.com/xlVideos01.html#FillBlanks

Thanks Dave and Ron for your excellent imput as always and i will have
a look at Debra Dalgleish's examples and it may be necessary to keep
the data after thinking about it.

Many thanks for all the help
Eddie