# Array search too slow

#### Pau905

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

### Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

#### Pau905

##### New Member
Here is an example

#### Attachments

• pic.jpg
97.4 KB · Views: 7

#### Fluff

##### MrExcel MVP, Moderator
VBA Code:
``````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

NumVal1Val2Val3
2​
aa1aa1aa1
3​
aa3aa3aa3
7​
aa2aa2aa2
9​
aa4aa4aa4
12​
aa5aa5aa5

#### Pau905

##### New Member

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:
VBA Code:
``````SELECT tblMutli.*
FROM tblPrimary LEFT JOIN tblMutli ON tblPrimary.Num = tblMutli.Num;``````

#### Pau905

##### New Member

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.
VBA Code:
``````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

##### MrExcel MVP, Moderator
Glad we could help & thanks for the feedback.

Replies
2
Views
342
Replies
3
Views
46
Replies
6
Views
166
Replies
6
Views
122
Replies
1
Views
65

1,132,910
Messages
5,655,902
Members
418,250
Latest member
Jebacmakro

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back