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

 

[Ron Rosenfeld]

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

A different approach would be to hide the duplicates, so that rows are
still labelled properly.

For example, if your data starts in A1,

Select A1
(next step varies depending on version of Excel):
Format/Conditional Formatting/Formula:

Formula: =COUNTIF($A$1:A1,A1)>1
Format the font to the same color as the background (nominally white).

Select A1 and the format painter. Copy the format down column A.

 

[Ron Rosenfeld]

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.

I see you had the same idea I did. Yours was posted first but I
didn't see it until after I had posted mine.

 

[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

 

[Ron Rosenfeld]

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

You're welcome. Thanks for the feedback.