By Vishnu Subind

2020-02-14 08:47:31 8 Comments

So I have two arrays. One of them is 1D (AllAssigneesUnique) and the other is 2D (DB_Array). I want to compare (AllAssigneesUnique) with the first column of (DB_Array) and when there is an exact match, store the string from the first and second column of (DB_Array) to a third 3D Array called (NewAssigneesArray). Additionally, the third column of (NewAssigneesArray) should have the string "New". Below is my code so far. P.S. How can I re-dimension the new array automatically since the number of matching strings will not always be the same? At the moment, I am using a previously made dictionary to get the exact number of matching strings.

Dim NewAssigneesArray() As Variant
ReDim NewAssigneesArray(1 To NewAssigneesList.count, 1 To 3)

For a = LBound(AllAssigneesUnique) To UBound(AllAssigneesUnique)
    For b = LBound(DB_Array, 1) To UBound(DB_Array, 1)
        If AllAssigneesUnique(a) = DB_Array(b, 1) Then
            For i = LBound(NewAssigneesArray) To UBound(NewAssigneesArray)
                NewAssigneesArray(i, 1) = DB_Array(b, 1)
                NewAssigneesArray(i, 2) = DB_Array(b, 2)
                NewAssigneesArray(i, 3) = "New"
            Next i
        End If
    Next b
Next a


@T.M. 2020-02-14 10:02:32

Alternative to ReDim Preserve

The correct solution of @Variatus uses an array with reversed row/column dimensions to overcome the limitation that ReDim Preserve only works at the last (here: 2nd) dimension.

Alternatively, I demonstrate a workaround which restructures the 1st dimension /i.e. rows/ directly (leaving the 2nd untouched) via Application.Index() function:

NewAssigneesArray= Application.Index(NewAssigneesArray, Evaluate("row(1:" & i & ")"), Array(1, 2, 3))

Related link

Read about some peculiarities of the the Application.Index() function" at Insert first column in datafield array without loops or API call

@Variatus 2020-02-14 09:44:24

The code below is untested for obvious reasons and might contain typos or small errors. I believe you will be able to correct them. Note that it's more efficient to dimension an array larger than required and give it its final size at the end. The large UBound doesn't require RAM space.

Sub CreateNewArray()

    Dim NewAssigneesArray() As Variant
    Dim i As Long
    Dim a As Long, b As Long

    ' set a (UBound, 2) a lot higher than what you will ever need.
    ' note that you can't Redim (UBound, 1), only (UBound, 2)
    ReDim NewAssigneesArray(1 To 3, 1 To 5000)

    For a = LBound(AllAssigneesUnique) To UBound(AllAssigneesUnique)
        For b = LBound(DB_Array, 1) To UBound(DB_Array, 1)
            ' Use VbBinaryCompare for a case sensitive comparison
            If StrComp(AllAssigneesUnique(a), DB_Array(b, 1), vbTextCompare) = 0 Then
                i = i + 1
                NewAssigneesArray(1, i) = DB_Array(b, 1)
                NewAssigneesArray(2, i) = DB_Array(b, 2)
                NewAssigneesArray(3, i) = "New"
                Exit For
            End If
        Next b
    Next a
    ReDim Preserve NewAssigneesArray(1 To 3, 1 To i)
End Sub

@T.M. 2020-02-17 11:10:43

FYI You might be interested in my work around to ReDim Preserve allowing restructuring the array's first dimension without loops :+) @Variatus

Related Questions

Sponsored Content

45 Answered Questions

[SOLVED] Sort array of objects by string property value

61 Answered Questions

[SOLVED] How can I concatenate two arrays in Java?

16 Answered Questions

[SOLVED] Copy array items into another array

  • 2010-11-11 15:33:18
  • bba
  • 990401 View
  • 880 Score
  • 16 Answer
  • Tags:   javascript arrays

78 Answered Questions

[SOLVED] How to merge two arrays in JavaScript and de-duplicate items

60 Answered Questions

[SOLVED] How to compare arrays in JavaScript?

37 Answered Questions

[SOLVED] Compare two dates with JavaScript

13 Answered Questions

[SOLVED] How can I add new array elements at the beginning of an array in Javascript?

  • 2011-11-10 00:35:22
  • Moon
  • 771665 View
  • 1530 Score
  • 13 Answer
  • Tags:   javascript arrays

19 Answered Questions

[SOLVED] Loop through an array of strings in Bash?

  • 2012-01-16 13:21:16
  • Mo.
  • 1169396 View
  • 1433 Score
  • 19 Answer
  • Tags:   arrays bash shell

42 Answered Questions

[SOLVED] How do you convert a byte array to a hexadecimal string, and vice versa?

  • 2008-11-22 10:03:13
  • alextansc
  • 828661 View
  • 1338 Score
  • 42 Answer
  • Tags:   c# arrays hex

Sponsored Content