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.

A	B	C	D	E	F	G etc	etc	etc	etc
39	6	23	34	2	7
26	8	3	35	18	22
7	23	2	39	6	34
3	16	39	40	6	12
22	17	9	27	10	2
23	34	6	2	7	39

<tbody>
</tbody>

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
1	39	6	23	34	2	7		2	6	7	23	34	39		2-6-7-23-34-39		3
2	26	8	3	35	18	22		3	8	18	22	26	35		3-8-18-22-26-35		1
3	7	23	2	39	6	34		2	6	7	23	34	39		2-6-7-23-34-39		3
4	3	16	39	40	6	12		3	6	12	16	39	40		3-6-12-16-39-40		1
5	22	17	9	27	10	2		2	9	10	17	22	27		2-9-10-17-22-27		1
6	23	34	6	2	7	39		2	6	7	23	34	39		2-6-7-23-34-39		3

<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>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula H1 =AGGREGATE(15,6,$A1:$F1,COLUMN()-7) O1 =H1&"-"&I1&"-"&J1&"-"&K1&"-"&L1&"-"&M1 Q1 =COUNTIF(O:O,O1) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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?