djapigo
02-06-2008, 05:15 PM
Hello all,
First time user and I must say that you all are doing a great job here... :bow:
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
First time user and I must say that you all are doing a great job here... :bow:
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