Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
how to delete duplicate records in a row
Hi. I was browsing the topics in the forum and most of the things I found are
deleting duplicates involve entire rows. My problem is this, I need to delete duplicate records by row. For instance, in my example below, I want to elimate the duplicate "20" under column C so that row 1 would only show 4 records (10, 20, 50 and 30). I'd appreciate any assistance anyonoe can provide. A B C D E 10 20 20 50 30 5 5 6 7 8 9 10 11 11 12 Thanks! Christian |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
how to delete duplicate records in a row
How about a little macro:
Option Explicit Sub testme01() Dim iRow As Long Dim iCol As Long Dim FirstRow As Long Dim LastRow As Long With Worksheets("sheet1") FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = FirstRow To LastRow For iCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column _ To 2 Step -1 If Application.CountIf(.Cells(iRow, 1).Resize(1, iCol), _ .Cells(iRow, iCol).Value) 1 Then .Cells(iRow, iCol).Delete Shift:=xlToLeft End If Next iCol Next iRow End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Christian wrote: Hi. I was browsing the topics in the forum and most of the things I found are deleting duplicates involve entire rows. My problem is this, I need to delete duplicate records by row. For instance, in my example below, I want to elimate the duplicate "20" under column C so that row 1 would only show 4 records (10, 20, 50 and 30). I'd appreciate any assistance anyonoe can provide. A B C D E 10 20 20 50 30 5 5 6 7 8 9 10 11 11 12 Thanks! Christian -- Dave Peterson |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
how to delete duplicate records in a row
Hi Dave,
Thanks for this one. It sure made my life easier and simpler. Again, thanks. Christian "Dave Peterson" wrote: How about a little macro: Option Explicit Sub testme01() Dim iRow As Long Dim iCol As Long Dim FirstRow As Long Dim LastRow As Long With Worksheets("sheet1") FirstRow = 1 LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = FirstRow To LastRow For iCol = .Cells(iRow, .Columns.Count).End(xlToLeft).Column _ To 2 Step -1 If Application.CountIf(.Cells(iRow, 1).Resize(1, iCol), _ .Cells(iRow, iCol).Value) 1 Then .Cells(iRow, iCol).Delete Shift:=xlToLeft End If Next iCol Next iRow End With End Sub If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Christian wrote: Hi. I was browsing the topics in the forum and most of the things I found are deleting duplicates involve entire rows. My problem is this, I need to delete duplicate records by row. For instance, in my example below, I want to elimate the duplicate "20" under column C so that row 1 would only show 4 records (10, 20, 50 and 30). I'd appreciate any assistance anyonoe can provide. A B C D E 10 20 20 50 30 5 5 6 7 8 9 10 11 11 12 Thanks! Christian -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting duplicate records | Excel Discussion (Misc queries) | |||
Find duplicate records in Excel 2003 | Excel Discussion (Misc queries) | |||
How do I delete duplicate records from an entire Excel workbook? | Excel Discussion (Misc queries) | |||
How do I find duplicate rows in a list in Excel, and not delete it | Excel Discussion (Misc queries) | |||
How to delete duplicate records when I merge two lists (deleting . | Excel Worksheet Functions |