drex79
02-17-2016, 12:39 PM
Hello everyone,
I have been trying to find a solution to a problem where I have 2 multi dimensional arrays with duplicate data. I have found a lot of resources on how to eliminate duplicates, but nothing on how to isolate them (duplicates is the information i need).
I have a quick example of what I am referring to.
Sub my_test_array()
Dim arr1(1 To 9, 1 To 2) As Variant
Dim arr2(1 To 9, 1 To 2) As Variant
Dim arr3() As Variant 'dynamic based on number of duplicates
'arr1 data
'rownum
arr1(1, 2) = "158"
arr1(2, 2) = "159"
arr1(3, 2) = "160"
arr1(4, 2) = "161" '<--- dupe found in arr2
arr1(5, 2) = "162" '<--- dupe found in arr2
arr1(6, 2) = "163" '<--- dupe found in arr2
arr1(7, 2) = "164" '<--- dupe found in arr2
arr1(8, 2) = "165" '<--- dupe found in arr2
arr1(9, 2) = "166" '<--- dupe found in arr2
'time
arr1(1, 1) = "12:15"
arr1(2, 1) = "12:30"
arr1(3, 1) = "12:45"
arr1(4, 1) = "13:00"
arr1(5, 1) = "13:15"
arr1(6, 1) = "13:30"
arr1(7, 1) = "13:45"
arr1(8, 1) = "14:00"
arr1(9, 1) = "14:15"
'arr2 data
'rownum
arr2(1, 2) = "161" '<--- dupe found in arr1
arr2(2, 2) = "162" '<--- dupe found in arr1
arr2(3, 2) = "163" '<--- dupe found in arr1
arr2(4, 2) = "164" '<--- dupe found in arr1
arr2(5, 2) = "165" '<--- dupe found in arr1
arr2(6, 2) = "166" '<--- dupe found in arr1
arr2(7, 2) = "167"
arr2(8, 2) = "168"
arr2(9, 2) = "169"
'time
arr2(1, 1) = "13:00"
arr2(2, 1) = "13:15"
arr2(3, 1) = "13:30"
arr2(4, 1) = "13:45"
arr2(5, 1) = "14:00"
arr2(6, 1) = "14:15"
arr2(7, 1) = "14:30"
arr2(8, 1) = "14:45"
arr2(9, 1) = "14:45"
'compare arrays (assuming loop) and isolate duplicates in arr3
'expected data in arr3
'rownum
arr3(1, 2) = "161"
arr3(2, 2) = "162"
arr3(3, 2) = "163"
arr3(4, 2) = "164"
arr3(5, 2) = "165"
arr3(6, 2) = "166"
'time
arr3(1, 1) = "13:00"
arr3(2, 1) = "13:15"
arr3(3, 1) = "13:30"
arr3(4, 1) = "13:45"
arr3(5, 1) = "14:00"
arr3(6, 1) = "14:15"
End Sub
I have a complex method to finding a range of data of interest which is dumped into 2 separate arrays (arr1 and arr2). I had been looking for a way to merge the 2 (and found that vba has no native way of doing such a thing. I thought it may be easier to loop through arr2 with the first value (rownum) from arr1, but figured that would be pretty intensive as I will be doing this over and over.
Anyone have any ideas on how to find the duplicates belonging to arr1 and arr2 and isolate them into arr3?
Thanks in advance!
I have been trying to find a solution to a problem where I have 2 multi dimensional arrays with duplicate data. I have found a lot of resources on how to eliminate duplicates, but nothing on how to isolate them (duplicates is the information i need).
I have a quick example of what I am referring to.
Sub my_test_array()
Dim arr1(1 To 9, 1 To 2) As Variant
Dim arr2(1 To 9, 1 To 2) As Variant
Dim arr3() As Variant 'dynamic based on number of duplicates
'arr1 data
'rownum
arr1(1, 2) = "158"
arr1(2, 2) = "159"
arr1(3, 2) = "160"
arr1(4, 2) = "161" '<--- dupe found in arr2
arr1(5, 2) = "162" '<--- dupe found in arr2
arr1(6, 2) = "163" '<--- dupe found in arr2
arr1(7, 2) = "164" '<--- dupe found in arr2
arr1(8, 2) = "165" '<--- dupe found in arr2
arr1(9, 2) = "166" '<--- dupe found in arr2
'time
arr1(1, 1) = "12:15"
arr1(2, 1) = "12:30"
arr1(3, 1) = "12:45"
arr1(4, 1) = "13:00"
arr1(5, 1) = "13:15"
arr1(6, 1) = "13:30"
arr1(7, 1) = "13:45"
arr1(8, 1) = "14:00"
arr1(9, 1) = "14:15"
'arr2 data
'rownum
arr2(1, 2) = "161" '<--- dupe found in arr1
arr2(2, 2) = "162" '<--- dupe found in arr1
arr2(3, 2) = "163" '<--- dupe found in arr1
arr2(4, 2) = "164" '<--- dupe found in arr1
arr2(5, 2) = "165" '<--- dupe found in arr1
arr2(6, 2) = "166" '<--- dupe found in arr1
arr2(7, 2) = "167"
arr2(8, 2) = "168"
arr2(9, 2) = "169"
'time
arr2(1, 1) = "13:00"
arr2(2, 1) = "13:15"
arr2(3, 1) = "13:30"
arr2(4, 1) = "13:45"
arr2(5, 1) = "14:00"
arr2(6, 1) = "14:15"
arr2(7, 1) = "14:30"
arr2(8, 1) = "14:45"
arr2(9, 1) = "14:45"
'compare arrays (assuming loop) and isolate duplicates in arr3
'expected data in arr3
'rownum
arr3(1, 2) = "161"
arr3(2, 2) = "162"
arr3(3, 2) = "163"
arr3(4, 2) = "164"
arr3(5, 2) = "165"
arr3(6, 2) = "166"
'time
arr3(1, 1) = "13:00"
arr3(2, 1) = "13:15"
arr3(3, 1) = "13:30"
arr3(4, 1) = "13:45"
arr3(5, 1) = "14:00"
arr3(6, 1) = "14:15"
End Sub
I have a complex method to finding a range of data of interest which is dumped into 2 separate arrays (arr1 and arr2). I had been looking for a way to merge the 2 (and found that vba has no native way of doing such a thing. I thought it may be easier to loop through arr2 with the first value (rownum) from arr1, but figured that would be pretty intensive as I will be doing this over and over.
Anyone have any ideas on how to find the duplicates belonging to arr1 and arr2 and isolate them into arr3?
Thanks in advance!