Combine array with lookup in VB

[djapigo]

Hello all,

First time user and I must say that you all are doing a great job here...

I'm a VB-wanna-be, so I apologize if this is "too simple".,,

I've looked and can't seem to find exactly what I'm trying to do...

I have a main spreadsheet that prompts the user to open up two separate data files. Once these 2 files are open, I simply read all the data in two different 2-D arrays. I have no problem here so far.
Array1:
ID DOB DOH Acct
1111 1/1/50 1/1/90 500.00
2222 1/1/51 1/1/90 100.00
5555 1/1/52 1/1/90 250.00

Array2:
ID Code1 Code2
2222 1 0
1111 0 1
3333 0 5
4444 1 0

... where Array2 may or may not have the same number of people as Array1 and may not be sorted like Array1...

... then I want to create Array3 that "combines" all the data while using Array1 as main array while Array2 is simply for lookup...

Array3
ID DOB DOH Acct Code1 Code2
1111 1/1/50 1/1/90 500.00 0 1
2222 1/1/51 1/1/90 100.00 1 0
5555 1/1/52 1/1/90 250.00

Once I have Array3, then I simply unload this into Data tab of the main spreadsheet.

I originally thought of simply having a nested loop searching for the same IDs? In other words,

Loop i = 1 to countArray1
Loop j = 1 to countArray2
If Array1(i).ID = Array2(j).ID then
"combine"
Endif
Next
Next

This seems like a very slow process, especially if I have thousands of IDs...

Is there a more "elegant" and more efficient way of solving this? Maybe with a sorting process first? Or is there a way to treat VB arrays like a "database" with ID as an "index"? Or am I simply missing a VB function that can do this?

Is there maybe a different approach here? The reason that I want arrays is that I want the main spreadsheet to be able to work stand alone, not rely on "formulas" in the Data tab.

Any reply will be greatly appreciated.

Thank you,
Dennis

[mikerickson]

The worksheet functions MATCH and INDEX work with arrays.

Application.Index(Array1,,1) returns the first column of Array1 as a 1-D array.

From your sample data
rowNum = Application.Match(5555,Application.Index(Array1,,1),0)
should return 3.

Further thought:
It might be easier to not construct Array3.

Put Array1 on the data sheet and then put Array2 (including the ID column) next to it on the data sheet

If you create a Custom List (Excel Options, Custom List tab) from the ID column of Array2.

Sort the 4 columns of Array1 on that custom list. If every ID in Array2 is also in Array1 (your OP said that Array2 might be smaller than Array1, that's OK, just no new IDs in Array2) , then the rows should be aligned and Array2's ID column can be deleted.

The Macro Recorder would be the starting point of turning the "further thought" into a macro.

[djapigo]

Thanks, mikerickson...

Let me take a look to see if this solves it...

Dennis