Remove duplicates in a row

[Danny54]

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

 

[JLGWhiz]

Assume data is in rows 2:10

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

 

[Danny54]

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

 

[Danny54]

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

 

[JLGWhiz]

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

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

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.

 

[Danny54]

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

 

[JLGWhiz]

See if this works

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

 

[Danny54]

no it didnt work - heres the data I tested with

 

[JLGWhiz]

this is my test data
Starting data

TestBase.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	x	x	x	x	x	x	x
2	x	x	x	x	x	x	x			abc	def	abc	ghi	def	ghi
3	x	x	x	x	x	x	x			def	ghi	jkl	def	ghi	jkl
4	x	x	x	x	x	x	x			jkl	mno	abc	abc	pqr
5	x	x	x	x	x	x	x			dog	cat	mouse	dog	cat
6	x	x	x	x	x	x	x			test1	test2	test3	test1	test4
Sheet1

After macro

TestBase.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	x	x	x	x	x	x	x
2	x	x	x	x	x	x	x			abc	def	ghi
3	x	x	x	x	x	x	x			def	ghi	jkl
4	x	x	x	x	x	x	x			jkl	mno	abc	pqr
5	x	x	x	x	x	x	x			dog	cat	mouse
6	x	x	x	x	x	x	x			test1	test2	test3	test4
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.

 

[DanteAmor]

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

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