Removing duplicates but leaving data on following rows
 

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

Removing duplicates but leaving data on following rows
 

On Jul 14, 6:34*am, webels wrote:
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

Removing duplicates but leaving data on following rows
 

On Jul 14, 3:46*pm, Don Guillett Excel MVP
wrote:
On Jul 14, 6:34*am, webels wrote:





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- Hide quoted text -

- Show quoted text -

Thank you Don,
This is perfect.

Eddie

Removing duplicates but leaving data on following rows
 

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/xlCondFor...html#Duplicate

On 07/14/2010 06:34, webels wrote:
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

--
Dave Peterson

Removing duplicates but leaving data on following rows
 

On Wed, 14 Jul 2010 04:34:37 -0700 (PDT), webels
wrote:

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.

Removing duplicates but leaving data on following rows
 

On Wed, 14 Jul 2010 12:30:20 -0500, Dave Peterson
wrote:

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.

Removing duplicates but leaving data on following rows
 

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

On 07/14/2010 12:39, Ron Rosenfeld wrote:
On Wed, 14 Jul 2010 12:30:20 -0500, Dave Peterson
wrote:

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

Removing duplicates but leaving data on following rows
 

On Jul 14, 9:38*pm, Dave Peterson wrote:
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

On 07/14/2010 12:39, Ron Rosenfeld wrote:

On Wed, 14 Jul 2010 12:30:20 -0500, Dave Peterson
*wrote:

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

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

Removing duplicates but leaving data on following rows
 

On Wed, 14 Jul 2010 16:08:03 -0700 (PDT), webels
wrote:

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.