Array search too slow

Pau905

New Member
Joined
Nov 10, 2020
Messages
16
Office Version
  1. 365
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!
 

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
Joined
Nov 10, 2020
Messages
16
Office Version
  1. 365
Here is an example
 

Attachments

  • pic.jpg
    pic.jpg
    97.4 KB · Views: 7

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,245
Office Version
  1. 365
Platform
  1. Windows
How about
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
 
Solution

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,552
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 10, 2020
Messages
16
Office Version
  1. 365

ADVERTISEMENT

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

Joe can write down the query that you used?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,552
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 10, 2020
Messages
16
Office Version
  1. 365

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,552
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
58,245
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top