Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete duplicated values in each row ???
I have a sheet with duplicated values in the rows for example: A B C D E F G H I J 1| 1| 1| 1| 3| 5| 5| 5| 1| 1| 1| 2| 8| 8| 8| 3| 3| 3| 8| 8| 8| 8| I need to delete all duplicated values in each row Result A B C D E F G H I J 1| 1| 3| 5| 1| 2| 8| 3| 8| -- Oleg ------------------------------------------------------------------------ Oleg's Profile: http://www.excelforum.com/member.php...fo&userid=4004 View this thread: http://www.excelforum.com/showthread...hreadid=520035 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete duplicated values in each row ???
That needs some macro.
Here is an example HTH -- AP '=========== cut ============== Sub supp() suppdupes range("A1:J2") End Sub Sub suppDupes(table As Range) Dim iRow As Long Dim iCol As Long With table For iRow = 1 To .Rows.Count For iCol = .Columns.Count To 2 Step -1 With .Cells(iRow, iCol) If .Value = .Offset(0, -1).Value _ Then .Delete xlShiftToLeft End With Next iCol Next iRow End With End Sub '=========================== "Oleg" a écrit dans le message de ... I have a sheet with duplicated values in the rows for example: A B C D E F G H I J 1| 1| 1| 1| 3| 5| 5| 5| 1| 1| 1| 2| 8| 8| 8| 3| 3| 3| 8| 8| 8| 8| I need to delete all duplicated values in each row Result A B C D E F G H I J 1| 1| 3| 5| 1| 2| 8| 3| 8| -- Oleg ------------------------------------------------------------------------ Oleg's Profile: http://www.excelforum.com/member.php...fo&userid=4004 View this thread: http://www.excelforum.com/showthread...hreadid=520035 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to delete duplicated values in each row ???
Sub Test()
Dim iLastRow As Long Dim iLastCol As Long Dim i As Long, j As Long iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = 1 To iLastRow iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column For j = iLastCol - 1 To 1 Step -1 If Cells(i, j).Value = Cells(i, j + 1).Value Then Cells(i, j + 1).Value = "" Cells(i, j + 2).Resize(, iLastCol - j).Copy Cells(i, j + 1) End If Next j Next i End Sub -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Oleg" wrote in message ... I have a sheet with duplicated values in the rows for example: A B C D E F G H I J 1| 1| 1| 1| 3| 5| 5| 5| 1| 1| 1| 2| 8| 8| 8| 3| 3| 3| 8| 8| 8| 8| I need to delete all duplicated values in each row Result A B C D E F G H I J 1| 1| 3| 5| 1| 2| 8| 3| 8| -- Oleg ------------------------------------------------------------------------ Oleg's Profile: http://www.excelforum.com/member.php...fo&userid=4004 View this thread: http://www.excelforum.com/showthread...hreadid=520035 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting Unique Values | Excel Worksheet Functions | |||
How to delete values of a cell if it is found in another coloumn | Excel Worksheet Functions | |||
Delete non duplicated rows | Excel Discussion (Misc queries) | |||
Find & delete duplicated entries. | Excel Discussion (Misc queries) | |||
Macro do delete values in cells | Excel Discussion (Misc queries) |