Remove duplicates in a row

Danny54

Active Member
Joined
Jul 3, 2019
Messages
295
Office Version
  1. 365
Platform
  1. Windows
whats the best way to remove duplicate data in each column specifying a starting and ending column

beginning sheet remove duplicates starting in row1 column J-P

J K L M N O P
1 cat dog mouse cat
2 bat car ball switch car ball key
3 man boy man woman
4 dog bat ball

output after removal done on row by row

J K L M N O P
1 cat dog mouse
2 bat car ball switch key
3 man boy woman
4 dog bat ball

Thanks
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Assume data is in rows 2:10
VBA Code:
Sub t()
Dim c As Range, i As Long
    For Each c In Range("A2:A10")
        For i = Cells(c.Row, Columns.Count).End(xlToLeft).Column To 2 Step -1
            If Application.CountIf(c.Resize(, i), Cells(c.Row, i).Value) > 1 Then
                Cells(c.Row, i).Delete xlShiftToLeft
            End If
        Next
    Next
End Sub
 
Upvote 0
Thanks so much for the response.

Looking at the code ... this would look for duplicates starting in Row A2 thru Row A10? looking at all columns (correct?)
how would i use a range of J1:Z999

Thanks again
 
Upvote 0
JLGWhiz,

thanks so much. I was playing around with the macro and realized the code functions like i wanted by just changing the range to J1:Q20. Ill study the code to learn from it.

Have a great day
 
Upvote 0
JLGWhiz,

thanks so much. I was playing around with the macro and realized the code functions like i wanted by just changing the range to J1:Q20. Ill study the code to learn from it.

Have a great day
If you have data in columns A:Z and you only want to elimnate duplicates in columns D:J then The 'For Each c' statement would use a column that best represents the rows in the columns D:J and the parmeters of the 'For i' statement would start with th last column number to the first column number, in this case
Code:
For i = 10 To 4 Step -1
. The 'Resize value would need to be adjusted by (i - (last column nuber - first column number)) or in the case
Code:
c.Resize(i - 6)
. This, of course would be where c represents a cell in the first column of the range. Otherwise, c would also need to be adusted with an offset to the first column so that the Resize will apply to the correct range of cells. The code simply checks each value of each row of the specified range to see if the item appears more than once in the row parameters.
 
Upvote 0
what the data looks like is

each sheet can contain many rows of data
col a - g stays the same and is unaffected for the removal of duplicates
col j thru then end row can contain data that i need to remove any duplicates found

do this for all rows of data.

Thanks
 
Upvote 0
See if this works
VBA Code:
Sub t3()
Dim c As Range, i As Long
    For Each c In Range("J2", Cells(Rows.Count, 10).End(xlUp))
        For i = Cells(c.Row, Columns.Count).End(xlToLeft).Column To c.Column Step -1
            If Application.CountIf(c.Resize(, i), Cells(c.Row, i).Value) > 1 Then
                Cells(c.Row, i).Delete xlShiftToLeft
            End If
        Next
    Next
End Sub
 
Upvote 0
no it didnt work - heres the data I tested with



1584564767660.png
 
Upvote 0
this is my test data
Starting data
TestBase.xlsm
ABCDEFGHIJKLMNO
1xxxxxxx
2xxxxxxxabcdefabcghidefghi
3xxxxxxxdefghijkldefghijkl
4xxxxxxxjklmnoabcabcpqr
5xxxxxxxdogcatmousedogcat
6xxxxxxxtest1test2test3test1test4
Sheet1

After macro
TestBase.xlsm
ABCDEFGHIJKLMNO
1xxxxxxx
2xxxxxxxabcdefghi
3xxxxxxxdefghijkl
4xxxxxxxjklmnoabcpqr
5xxxxxxxdogcatmouse
6xxxxxxxtest1test2test3test4
Sheet1


Leading or trailing spaces in your cells could be the problem. I'll see if I can come up with a remedy for that.
 
Upvote 0
no it didnt work - heres the data I tested with

The JLGWhiz macro works, only it starts at row 2 and as JLGWhiz commented, you should check if you have blank spaces before or after each data inside the cell.
What result do you get with your data after running the macro?

Here is another macro for you to consider

VBA Code:
Sub Uniques()
  Dim a As Variant, b As Variant, dic As Object
  Dim i As Long, j As Long, k As Long, lc As Long, lr As Long
  '
  Set dic = CreateObject("Scripting.dictionary")
  lc = ActiveSheet.Cells.Find("*", , xlValues, , xlByColumns, xlPrevious).Column
  lr = ActiveSheet.Range("J" & Rows.Count).End(3).Row
  '
  a = Range(Cells(1, "J"), Cells(lr, lc)).Value2
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2))
  '
  For i = 1 To UBound(a, 1)
    k = 1
    For j = 1 To UBound(a, 2)
      If Not dic.exists(i & "|" & Trim(a(i, j))) Then
        dic(i & "|" & Trim(a(i, j))) = Empty
        b(i, k) = Trim(a(i, j))
        k = k + 1
      End If
    Next
  Next
  '
  Range("J1").Resize(UBound(b, 1), UBound(b, 2)).Value = b
End Sub
 
Upvote 0

Forum statistics

Threads
1,224,817
Messages
6,181,148
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top