How to check this database for duplicates?

[exceloony]

Hi all. I prepared a small example database but it seems that I'm not allowed to post an attachment so I'll try and explain my dilemma by using one of the above tables.

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G etc
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[TD]etc
[/TD]
[/TR]
[TR]
[TD]39
[/TD]
[TD]6
[/TD]
[TD]23
[/TD]
[TD]34
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26
[/TD]
[TD]8
[/TD]
[TD]3
[/TD]
[TD]35
[/TD]
[TD]18
[/TD]
[TD]22
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]23
[/TD]
[TD]2
[/TD]
[TD]39
[/TD]
[TD]6
[/TD]
[TD]34
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]16
[/TD]
[TD]39
[/TD]
[TD]40
[/TD]
[TD]6
[/TD]
[TD]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]17
[/TD]
[TD]9
[/TD]
[TD]27
[/TD]
[TD]10
[/TD]
[TD]2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]34
[/TD]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]7
[/TD]
[TD]39
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]













This is a typical example of winning Lotto results over 7 draws of a 6-pick, 40-number lottery.
(The real database contains about 1500 draws).
I'm trying to find out if any of the 1500 results have ever been duplicated.
For example, rows 1,3 and 6 contain the same numbers.
But they're in different numerical order, as is almost certain to be the case in real life.
Could a formula recognize the duplicated 6 numbers even though their order is different?

 

[Toadstool]

You could use some worker columns.
The AGGREGATE puts the numbers in ascending sequence.
The concatenation gives a single cell to count (with "-" between so 3-34 isn't confused with 33-4).
The COUNTIF tells how many repeats there are.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
2-6-7-23-34-39
3-8-18-22-26-35
2-6-7-23-34-39
3-6-12-16-39-40
2-9-10-17-22-27
2-6-7-23-34-39

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]18[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]26[/TD]
[TD="align: center"]35[/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]27[/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]1[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]23[/TD]
[TD="align: center"]34[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"][/TD]

[TD="align: center"][/TD]
[TD="align: center"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H1[/TH]
[TD="align: left"]=AGGREGATE(15,6,$A1:$F1,COLUMN()-7)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]O1
[/TH]
[TD="align: left"]=H1&"-"&I1&"-"&J1&"-"&K1&"-"&L1&"-"&M1[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]Q1
[/TH]
[TD="align: left"]=COUNTIF(O:O,O1)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[lrobbo314]

Same idea, but with a UDF.

VBA:

Function ALSORT(r As Range) As String
With CreateObject("System.Collections.ArrayList")
    
    For Each c In r
        .Add c.Value
    Next c
    
    .Sort
    ALSORT = Join(.toarray, "-")
    
End With
End Function

Book1
	A	B	C	D	E	F	G	H
1	39	6	23	34	2	7	2-6-7-23-34-39	1
2	26	8	3	35	18	22	3-8-18-22-26-35	1
3	7	23	2	39	6	34	2-6-7-23-34-39	2
4	3	16	39	40	6	12	3-6-12-16-39-40	1
5	22	17	9	27	10	2	2-9-10-17-22-27	1
6	23	34	6	2	7	39	2-6-7-23-34-39	3
Sheet1

Cell Formulas
Range	Formula
G1	=ALSORT(A1:F1)
G2	=ALSORT(A2:F2)
G3	=ALSORT(A3:F3)
G4	=ALSORT(A4:F4)
G5	=ALSORT(A5:F5)
G6	=ALSORT(A6:F6)
H1	=COUNTIF($G$1:G1,G1)
H2	=COUNTIF($G$1:G2,G2)
H3	=COUNTIF($G$1:G3,G3)
H4	=COUNTIF($G$1:G4,G4)
H5	=COUNTIF($G$1:G5,G5)
H6	=COUNTIF($G$1:G6,G6)

 

[exceloony]

Thanks guys. I just posted a longer reply but it seems to have disappeared, hence this post to see if it works...

 

[exceloony]

Okay, I know what I did - clicked 'Reply to Thread' instead of 'Submit Reply'.

Toadstool: Ah, so it's necessary to put each row into numerical order before anything else? It occurred to me that one could then 'sort' column H into low-to-high which would sift the database into 1,x,x,x,x,x, 2,x,x,x,x,x etc groups from the top down. Though with a database of 1500 entries and 40 numbers I guess there'd be around 37 or so results (1500\40) in each group starting with 1, then 2 and so on. Your way's best!

Irobbo341: Thanks for the alternative method. Would that work with a 1500 row database?