Search for repetitions in a two-dimensional array, or the right tool

Good day.

In varying degrees, interested in algorithms. Stumbled upon a fresh article
"Search for repetitions in a two-dimensional array, or computational complexity on an example" http://habrahabr.ru/post/141258/ . The author of the article , Singerofthefall , quite interestingly talks about solving the problem and optimizing the algorithm. Very interesting. However, in my opinion, first of all, it was necessary to determine not the algorithm, but the tool that will solve the problem. And so the tool was chosen wrong, hence all the complexity and optimization.
To solve the author's problem, the database tools were most suitable, respectively, and it was necessary to use them.

There are 2 possible ways.

1. Referring to the xls file as a database, you can read more here http://vbadud.blogspot.com/2008/05/using-excel-as-database.html
2. Distillation of data in the database and processing and subsequent output.

Because at work I often encounter a similar task, processing a two-dimensional array, but not in xls files, but in AutoCAD and with coordinates, then I will try to show how it works.

Simplest solution

1. We create a DB, for simplicity I use DAO. If speed is critical, it makes sense to create a base on Ramdisk.
2. Create a table for receiving data
3. We overtake the initial data in the table
4. We execute the simplest SQL query, grouping and sorting data.
5. Display the data.

')

'  ,       IDE VBA      "Ioeaea! Auiieiaiea i?ia?aiiu i?a?aaii!" Sub grid() Dim retObj As AcadObject Dim retPnt As Variant Dim db As DAO.Database Dim rst As Recordset Dim ssetObj As AcadSelectionSet Dim Items As Object Dim handle As String mesto_db = Environ("APPDATA") & "\" name_db = Environ("UserName") & "_grid" Set fs1 = CreateObject("Scripting.FileSystemObject") fs1.CreateTextFile mesto_db & name_db & ".mdb" fs1.DeleteFile mesto_db & name_db & ".mdb" Set db = DAO.CreateDatabase(mesto_db & name_db & ".mdb", dbLangCyrillic) db.Execute "CREATE TABLE Tabl1 " & "(x REAL, y REAL, h CHAR(10));" On Error Resume Next Set ssetObj = ThisDrawing.SelectionSets("Boxa") If Err <> 0 Then Err.Clear Set ssetObj = ThisDrawing.SelectionSets.Add("Boxa") End If ssetObj.Clear ssetObj.SelectOnScreen On Error GoTo fuck Dim temp_block As AcadBlockReference For Each item In ssetObj If item.ObjectName = "AcDbBlockReference" Then If item.EffectiveName = "SV" Then Attributes = item.GetAttributes BlockProperties = item.GetDynamicBlockProperties point = item.insertionPoint point1 = CLng(point(0)) point2 = CLng(point(1)) Set temp_block = item handle = CStr(temp_block.handle) db.Execute "INSERT INTO Tabl1 (x,y,h) VALUES (" & point1 & ", " & point2 & ", \'" & handle & "\');" End If End If Next Set rst = db.OpenRecordset("SELECT x, y, h FROM Tabl1 GROUP BY x, y, h ORDER BY x, y, h ;") If rst.RecordCount > 0 Then rst.MoveFirst Do While Not rst.EOF = True X0 = rst.Fields(0) Y0 = rst.Fields(1) rst.MoveNext Loop End If fuck: If Err <> 0 Then ThisDrawing.Utility.Prompt (vbCrLf & "Error!" & vbCrLf) rst.Close db.Close Set db = Nothing ssetObj.Clear ssetObj.Delete End Sub 

Execution speed:

Number of points - lead time
100 * 100 - 5.89 seconds
200 * 200 - 24.73 seconds
400 * 200 - 47.33 seconds
Linear dependence

Conclusion:

The conclusions in the article to which I referred at the beginning are very good and I use both of them for them.
I will add only the third point, that always the best result will give the most suitable tool.

PS

1. In the note to the tag (source) VBA though not specified, but it works.
2. You can use the Collection to track repetitions in an array.

  Dim x_col As New Collection Dim txt_arr() As Variant For Q = 1 To UBound(txt_arr) x_col_Item = txt_arr(Q) x_col.Add x_col_Item, CStr(x_col_Item) Next 

When adding an item to the collection with a key already in it, you will get an error. Processing it will get a set of only repetitive elements.