remove shuffled duplicates

[vs140580]

Suppose a1,b1,c1,d1,e1 are elements of a row and a1,c1,e1,d1,b1 is some permutation of the elements of that row how to retain only one row among all such permutations in several rows and delete remaining I dont know VBA prograoming kind help.

 

[sheetspread]

Excel 2007 and later has remove duplicates in the data tab, for earlier versions the advanced filter will do this

 

[Peter_SSs]

Excel 2007 and later has remove duplicates in the data tab, for earlier versions the advanced filter will do this

I'd be interested to see how you envisage using either option for this problem.
.. or perhaps I have misunderstood it.

 

[Peter_SSs]

My interpretation is that for the following data, you would want to keep the green rows (unique combinations)
and delete the yellow rows (row 4 is a permutation of row 2 & row 6 is a permutation of row 3)

Excel Workbook
	A	B	C	D	E
1	Hdr 1	Hdr 2	Hdr 3	Hdr 4	Hdr 5
2	a1	b1	c1	d1	e1
3	a1	b1	x1	j1	e1
4	a1	c1	e1	d1	b1
5	z1	z2	z3	z4	z5
6	x1	j1	e1	a1	b1
Remove Permutations

If so, then try ..

Sub DeletePerputations()
  Dim al As Object, d As Object
  Dim Data As Variant, tmp As Variant
  Dim i As Long, j As Long
  Dim Comb As String
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  Set al = CreateObject("System.Collections.ArrayList")
  With Range("A1").CurrentRegion
    Data = .Value
    ReDim tmp(1 To UBound(Data), 1 To 1)
    For i = 1 To UBound(Data)
      For j = 1 To UBound(Data, 2)
        al.Add Data(i, j)
      Next j
      al.Sort
      Comb = Join(al.toarray(), "|")
      If Not d.exists(Comb) Then
        tmp(i, 1) = i
        d.Add Comb, 1
      End If
      al.Clear
    Next i
    Application.ScreenUpdating = False
    With .Resize(, .Columns.Count + 1)
      .Columns(.Columns.Count).Value = tmp
      .Sort Key1:=.Columns(.Columns.Count), Order1:=xlAscending, Header:=xlNo, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
      With .Columns(.Columns.Count)
        On Error Resume Next
        .SpecialCells(xlBlanks).EntireRow.Delete
        On Error GoTo 0
        .ClearContents
      End With
    End With
    Application.ScreenUpdating = True
  End With
End Sub

After the code:

Excel Workbook
	A	B	C	D	E
1	Hdr 1	Hdr 2	Hdr 3	Hdr 4	Hdr 5
2	a1	b1	c1	d1	e1
3	a1	b1	x1	j1	e1
4	z1	z2	z3	z4	z5
5
6
Remove Permutations

 

[sheetspread]

I'd be interested to see how you envisage using either option for this problem.

Well your code is better, but after a formula:

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K
1							1	2	3	4	5
2	a1	b1	c1	d1	e1		a1	b1	c1	d1	e1
3	q1	w1	e1	r1	t1		e1	q1	r1	t1	w1
4	b1	a1	d1	c1	e1		a1	b1	c1	d1	e1
5	z	x	c1	v	b		b	c1	v	x	z
6	a1	s	d1	f	g		a1	d1	f	g	s
7	e1	a1	b1	d1	c1		a1	b1	c1	d1	e1
8	p	o	i	u	y		i	o	p	u	y
9	m	n	b1	v	c1		b1	c1	m	n	v
Sheet7 (5)

Cell Formulas
Range	Formula
G2	{=INDEX($A2:$E2,MATCH(COLUMN(A2),COUNTIF($A2:$E2,"<="&$A2:$E2),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

the advanced filter can hide the sorted group duplicate rows, and the originals can be copied with F5-special-visible. My above suggestion did imply that no sorting would be required, and it seems that remove duplicates won't work because there's no way to remember which rows were deleted. So I was 1/3 right

 

[Peter_SSs]

Well your code is better, but after a formula:


the advanced filter can hide the sorted group duplicate rows, and the originals can be copied with F5-special-visible. My above suggestion did imply that no sorting would be required, and it seems that remove duplicates won't work because there's no way to remember which rows were deleted. So I was 1/3 right

Well that does at least give the OP a chance - I thought that you original response was a bit light-on with help.

I have 3 comments.

1. As you have noted, Remove Duplicates is no use because you have created a different range. However, I would warn about ever recommending Remove Duplicates as it is known to fail (without indication) in some circumstances. As far as I know it has not yet been fixed. An example can be found here.

2. I don't know if it is possible with the OPs data, but your sorting formula fails if there are repeated entries in a row. eg Change cell D2 in your layout above to "b1"

3. I would recommend using a COLUMNS() function instead of COLUMN() in your formula. To see why, with your sheet and formulas as shown above, insert a new column A to the left of the data.

 

[vs140580]

THe question now is if they are spread across workbooks due to large data files

 

[Peter_SSs]

THe question now is if they are spread across workbooks due to large data files

That isn't terribly informative but in any case would make me think it is time to use Access or perhaps one of the Power BI tools.

 

[sheetspread]

1. I would warn about ever recommending Remove Duplicates as it is known to fail (without indication) in some circumstances. As far as I know it has not yet been fixed.

Ok that's not good

your sorting formula fails if there are repeated entries in a row. eg Change cell D2 in your layout above to "b1"

3. I would recommend using a COLUMNS() function instead of COLUMN() in your formula. To see why, with your sheet and formulas as shown above, insert a new column A to the left of the data.

I remember there was a reason why so many used columns instead of column, and I should have gone with the first option here: http://www.mrexcel.com/forum/excel-questions/706066-simple-formula-sort-text.html which seems to be better with repeats: =INDEX($A2:$E2,MATCH(SMALL(COUNTIF($A2:$E2,"<="&$A2:$E2),COLUMNS($A2:A2)),COUNTIF($A2:$E2,"<="&$A2:$E2),0))

THe question now is if they are spread across workbooks due to large data files

How difficult would it be to consolidate them into one with perhaps a reference number for each line?

 

[vs140580]

So only trying oracle PL sql still I need to do a procedure there may need help