Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Dups in one column then delete leaving one
I have a list of Vendor numbers in Column A, I need to find the duplicates
that are in column A then delete the duplicates but leave one. How and where do I write the formula for this? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Dups in one column then delete leaving one
If you are using Excel 2007, select column A, then select Remove Duplicates
on the Data ribbon. In Excel 2003 & earlier, use 2 helper (empty) columns on the sheet that has the duplicates. Enter this formula in row 1 in one helper column: =Row() Enter this formula in row 1 in the other helper column: =IF(COUNTIF($A$1:$A1,A1)1,0,1) Copy these two cells down through the last row of data (maybe the last vendor number in column A). Press F9 to recalculate the spreadsheet. Now select the two helper columns. Copy & paste special them in place as values. Select all the data on the sheet and sort it by the second helper column (the one that had the COUNTIF formula). Delete all the rows with a zero in that column - these are the duplicates. Sort all the data again by the first helper column; this puts the rows back in their original order minus the deleted duplicates. Finally, delete the two helper columns. Hope this helps, Hutch "Marc" wrote: I have a list of Vendor numbers in Column A, I need to find the duplicates that are in column A then delete the duplicates but leave one. How and where do I write the formula for this? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Dups in one column then delete leaving one
You can use code like the following:
Sub DeleteDupRows() Dim StartRow As Long Dim EndRow As Long Dim RNdx As Long With Application .ScreenUpdating = False .Calculation = xlCalculationManual .EnableEvents = False End With StartRow = 1 EndRow = Cells(StartRow, "A").End(xlDown).Row For RNdx = EndRow To StartRow Step -1 If Application.CountIf(Range(Cells(1, "A"), _ Cells(RNdx, "A")), Cells(RNdx, "A").Value) 1 Then Rows(RNdx).Delete End If Next RNdx With Application .ScreenUpdating = True .Calculation = xlCalculationAutomatic .EnableEvents = True End With End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional, Excel, 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com On Tue, 13 Apr 2010 10:26:01 -0700, Marc wrote: I have a list of Vendor numbers in Column A, I need to find the duplicates that are in column A then delete the duplicates but leave one. How and where do I write the formula for this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete Dups | Excel Discussion (Misc queries) | |||
delete a duplicate in column while leaving other duplicates | Excel Worksheet Functions | |||
Find duplicates, sum and delete dups | Excel Programming | |||
How to delete values in each row leaving only last value ? | Excel Discussion (Misc queries) | |||
How to delete rows with repeating values and leaving only one with highest value on the next column? | Excel Programming |