| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| delete all dulplicate rows leaving no record of the duplicates | Excel Discussion (Misc queries) | |||
| delete a duplicate in column while leaving other duplicates | Excel Worksheet Functions | |||
| Sorting a collection. *Leaving Duplicates* | Excel Programming | |||
| Removing Near-Duplicate Rows, Leaving Those w/Most Data in Specific Columns | Excel Discussion (Misc queries) | |||
| Scanning for Duplicate Data in a Column, Merging Data and Finally Removing All Duplicates...? | Excel Programming | |||
Linear Mode
