VBA::consolidating arrays

Question

I am creating a search function that allows users to search up to 3 different properties at the same time in a database (prop1,2 and 3) and I have created this sub in VBA by putting the results for a searched prop into an array. However, now that I have up to 3 arrays I need to consolidate the arrays so that only the data that is duplicated in the arrays are displayed in the results. Is there any advice on how to 1) only look at the arrays for the properties that the user is searching for and 2) take only the data that is repeated into a final array so I can display it in a results range? Any help is greatly appreciated! Thanks!

Answer 1

Assuming that your entries are directly from a database and therefore are unique for one property, I can think of following steps for a simple solution:

1. Merge Arrays together (prop1, prop2, prop3 > temp)
2. Count occurrences for each element (in this example code tempCount)

3. Based on the knowledge about the occurrences, create the final array (here called result)

   Dim prop1() As Variant
   Dim prop2() As Variant
   Dim prop3() As Variant
   Dim temp() As Variant
   Dim tempCount() As Integer
   Dim result() As Variant
   
   ReDim temp(UBound(prop1) + UBound(prop2) + UBound(prop3) + 1)
   
   'merge arrays
   Dim i As Integer
   On Error Resume Next
       For i = 0 To UBound(temp)
       temp(i * 3) = prop1(i)
       temp(i * 3 + 1) = prop2(i)
       temp(i * 3 + 2) = prop3(i)
   Next i
   
   'count occurences
   ReDim tempCount(UBound(temp) + 1)
   Dim j As Integer
   For i = 0 To UBound(temp)
   tempCount(i) = 1
   For j = 0 To i - 1
   
   'comparison of elements
       If temp(i) = temp(j) Then
       tempCount(i) = tempCount(i) + 1
       End If
   Next j
   Next i
   
   ReDim result(UBound(temp) + 1)
   
   'if an element occurs 3 times, add it to result
   Dim count As Integer
   count = 0
   For i = 0 To UBound(tempCount)
       If tempCount(i) = 3 Then
           result(count) = temp(i)
           count = count + 1
       End If
   Next i
   

To check for some samples I added this to the code. It simply prints out the arrays temp, result and tempCount to the columns A, B and C.

'some sample arrays
prop1 = Array("a", "b", "c", "d", "e")
prop2 = Array("b", "c", "f")
prop3 = Array("b", "c", "d", "g")

'some sample Output

'temp
Cells(1, 1).Value = "temp:"
For i = 0 To UBound(temp)
    Cells(i + 2, 1).Value = temp(i)
Next i

'result
Cells(1, 2).Value = "result:"
For i = 0 To UBound(result)
    Cells(i + 2, 2).Value = result(i)
Next i

'count:
Cells(1, 3).Value = "count:"
For i = 0 To UBound(tempCount)
    Cells(i + 2, 3).Value = tempCount(i)
Next i

Notes: tempCount just holds the cumulative number of occurrences at the point the element is watched at.