Array search too slow

[Pau905]

Hello All,

In got a problem with the code below. It must be much faster. I got my primary array with numbers (100000 rows). After that I got a multi dim array that includes part of the numbers from the primary array in the first column. I would like to make the the multi dim array to have the same numbers of rows and the numbers to align with the primary array.

The code below works for one column but is to slow
For j = LBound(v, 1) To 1000 UBound(v, 2)
Pos = Application.Match(v(0, j), ArrayWithNumbers, False)
If Not IsError(Pos) Then
v3(0, Pos) = v(0, j)
Else
End If
Next j

Example:
Primary array Multidim array (before) Multidim array (after)
1 2 aa
2 5 ff 2 aa
3 6 jj
4
5 5 ff
6 6 jj


Please help!

 

[Pau905]

Here is an example

 

[Fluff]

How about

Sub Pau()
   Dim Ary As Variant, Dary As Variant, Nary As Variant
   Dim r As Long, c As Long, nr As Long
   
   Ary = Range("A2", Range("A" & Rows.Count).End(xlUp)).Value2
   Dary = Range("C2:F" & Range("C" & Rows.Count).End(xlUp).Row).Value2
   ReDim Nary(1 To UBound(Ary), 1 To UBound(Dary, 2))
   With CreateObject("scripting.dictionary")
      For r = 1 To UBound(Ary)
         .Item(Ary(r, 1)) = r
      Next r
      For r = 1 To UBound(Dary)
         If .Exists(Dary(r, 1)) Then
            nr = .Item(Dary(r, 1))
            For c = 1 To UBound(Dary, 2)
               Nary(nr, c) = Dary(r, c)
            Next c
         End If
      Next r
   End With
   Range("H2").Resize(UBound(Nary), UBound(Nary, 2)).Value = Nary
End Sub

 

[Joe4]

This can be done pretty easily in Microsoft Access, with no VBA required.
Simply do a Left Outer Join between the two tables, and return everything from them "Multi Array" table.

Query3 Query3

Num	Val1	Val2	Val3
​
2​	aa1	aa1	aa1
3​	aa3	aa3	aa3
​
​
​
7​	aa2	aa2	aa2
​
9​	aa4	aa4	aa4
​
​
12​	aa5	aa5	aa5
​

 

[Pau905]

Thanks for the respone Fluff/Joe4! I will try your code Fluff!

Joe can write down the query that you used?

 

[Joe4]

OK. I called the field in each table that you are joining on "Num", and the two tables "tblPrimary" and "tblMulti".

So the SQL code of that query simply looks like this:

SELECT tblMutli.*
FROM tblPrimary LEFT JOIN tblMutli ON tblPrimary.Num = tblMutli.Num;

 

[Pau905]

Thanks both of you! I tested first Fluffs code because I did not understand exactly what to do with the query when not all columns should be selected.

Fluffs code worked very well!

Thanks very very much!

PS: Do you have a solution for my other problem as well ?
(Import Excel data to an MS Access Table too slow!)

 

[Joe4]

I did not understand exactly what to do with the query when not all columns should be selected.

Sorry, based on your other question, I assumed that you might already know a little bit about Access.

You would simply select which fields you want to display, i.e.

SELECT tblMutli.Num, tblMulti,Field1, tblMulti.Field2, tblMulti.Field3
FROM tblPrimary LEFT JOIN tblMutli ON tblPrimary.Num = tblMutli.Num;

You can use the Query Builder to do this part, if you are not comfortable editing VBA code.

PS: Do you have a solution for my other problem as well ?
(Import Excel data to an MS Access Table too slow!)

I guess you missed that I already replied to that.

 

[Fluff]

Glad we could help & thanks for the feedback.