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

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

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