# How to check this database for duplicates?

#### exceloony

##### New Member
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?

### Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

##### Well-known Member
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.

ABCDEFGHIJKLMNOPQ
13962334272672334392-6-7-23-34-393
2268335182238182226353-8-18-22-26-351
37232396342672334392-6-7-23-34-393
4316394061236121639403-6-12-16-39-401
5221792710229101722272-9-10-17-22-271
62334627392672334392-6-7-23-34-393

</tbody>
Sheet1

Worksheet Formulas
CellFormula
H1=AGGREGATE(15,6,\$A1:\$F1,COLUMN()-7)
O1
=H1&"-"&I1&"-"&J1&"-"&K1&"-"&L1&"-"&M1
Q1
=COUNTIF(O:O,O1)

</tbody>

<tbody>
</tbody>

#### lrobbo314

##### Well-known Member
Same idea, but with a UDF.

VBA:
Code:
``````Function ALSORT(r As Range) As String
With CreateObject("System.Collections.ArrayList")

For Each c In r
Next c

.Sort
ALSORT = Join(.toarray, "-")

End With
End Function``````

#### exceloony

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

#### exceloony

##### New Member

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?