Function to find index of Strings in an Array

[CluelessInVBA]

Greetings,

I have a set of IDs, stored in a 1D-Array A. The IDs are already sorted alphabetically and all of them are unique. Now, I want to find the index of said IDs in another array B, where the IDs are although sorted alphabetically, but many of them are duplicated (E.G. ID "DESHPDHK0003idyj" is 3 times in the Array B). Array A is just Array B, on which I used a function to remove duplicates (RemoveDupes). Usually, I would just use the Match function to find the indices of said IDs, but it doesn't work, since some IDs are basically named the same ("DESHPDHK0003idyj" and "DESHPDHK0003idYJ") but are written in different uppercase and lowercase letters. When using Match, it would just return ONE index for "DESHPDHK0003idyj" and "DESHPDHK0003idYJ" when in reality, it should return two indices.

The function to remove dupes also worked, since it didn't remove "false" duplicates (e.g. "DESHPDHK0003idyj" and "DESHPDHK0003idYJ"). I've also tried using the code of the RemoveDupes function to find a solution, but I couldn't. Here is the snippet of said function, if needed:

Function RemoveDupes(InputArray) As Variant


Dim OutputArray As Variant
Dim CurrentValue As Variant
Dim A As Variant


On Error Resume Next
OutputArray = Array("")
For Each CurrentValue In InputArray
Flag = 0
If IsEmpty(CurrentValue) Then GoTo skip
For Each A In OutputArray
If A = CurrentValue Then
Flag = 1
Exit For
End If
Next A

If Flag = 0 Then
ReDim Preserve OutputArray(UBound(OutputArray, 1) + 1)
OutputArray(UBound(OutputArray, 1) - 1) = CurrentValue
End If


skip:
Next
RemoveDupes = OutputArray

Thank you all in advance!

 

[johnnyL]

@CluelessInVBA, we need a few questions answered.

1) Is array B a 2 dimensional array? Or is it a 1 dimensional array like array A
2) The what and where:
2a) What exactly do you want displayed for the result? Do you want the Value that is duplicated displayed as well as the original index and subsequent indexes that match? Or do you want the Value that is duplicated as well as the subsequent indexes that match?
2b) Where do you want the results displayed? What address range?
3) You said array B has already been sorted, correct?

Please give examples of what you want to see for results.

ArrayB(1) = "Blue"
ArrayB(2) = "Green"
ArrayB(3) = "Green"
ArrayB(4) = "Green"
ArrayB(5) = "Green"
ArrayB(6) = "Orange"
ArrayB(7) = "Red"
ArrayB(8) = "Red"

What would your desired results for that look like?

 

[CluelessInVBA]

@CluelessInVBA, we need a few questions answered.

1) Is array B a 2 dimensional array? Or is it a 1 dimensional array like array A
2) The what and where:
2a) What exactly do you want displayed for the result? Do you want the Value that is duplicated displayed as well as the original index and subsequent indexes that match? Or do you want the Value that is duplicated as well as the subsequent indexes that match?
2b) Where do you want the results displayed? What address range?
3) You said array B has already been sorted, correct?

Please give examples of what you want to see for results.

ArrayB(1) = "Blue"
ArrayB(2) = "Green"
ArrayB(3) = "Green"
ArrayB(4) = "Green"
ArrayB(5) = "Green"
ArrayB(6) = "Orange"
ArrayB(7) = "Red"
ArrayB(8) = "Red"

What would your desired results for that look like?

@johnnyL , thank you for your reply!

1) B is also a 1D-Array, just like A.
2a) I see the issue here, I didn't clarify in the post. Whoops! As result, I want the first row of each ID in the array B. E.g. I have the ID "XYZ004" from array A and I now want to find the first row in array B where that ID ("XYZ004") appears. So for:

B(63)="XYZ003"
B(64)="XYZ004"
B(65)="XYZ004"
B(66)="XYZ004"
B(67)="xyz005"
B(68)="XYZ005"

I only want the row number 64 as a result. Also, if I wanted to find "XYZ005" in this case, I would get row 68 and not row 67, since one is in uppercase letters and one is not.

2b) The results should be displayed in a separate array (e.g. NewArray), in a way like this:

NewArray = findIndex(A, B) (find indices/rows of array A in B)

I'm honestly not sure what you mean with adress range, the size of the NewArray? If so, the NewArray should have the same dimensions as A.
3) Yes, Array B has already been sorted in the same way Array A is, the difference being, that B contains the duplicates, while A doesn't.

So the results should be something like this:

NewArray(1) = 1
NewArray(2) = 4
NewArray(3) = 8
...

when the base (B) is like this:

B(1) = xyz001
B(2) = xyz001
B(3) = xyz001
B(4) = XYZ001
B(5) = XYZ001
B(6) = XYZ001
B(7) = XYZ001
B(8) = XYZ002

Again, thank you a lot for your help! I really appreciate it!

 

[johnnyL]

So your question is not really about duplicates, it sounds like it is the opposite, meaning Uniques.

You just want another 1D array that contains the indexes of the first occurence of each unique value. Is that correct?

 

[CluelessInVBA]

So your question is not really about duplicates, it sounds like it is the opposite, meaning Uniques.

You just want another 1D array that contains the indexes of the first occurence of each unique value. Is that correct?

Yes, exactly!

 

[johnnyL]

This code should load the indexes of all unique values found in the array 'B'.

'   Declare Variables
    Dim BSlot                       As Long
    Dim MultiDuplicateCounter       As Long
    Dim CurrentArrayValue           As String
    Dim NewArray                    As Object
'
    Set NewArray = CreateObject("System.Collections.ArrayList")
'
    For BSlot = 1 To UBound(B)
'
        On Error GoTo LastEntry
        If B(BSlot) = B(BSlot + 1) Then                                                                         '   If match found then ...
            NewArray.Add BSlot
            CurrentArrayValue = B(BSlot)                                                                        '       Save the index value
            BSlot = BSlot + 1                                                                                   '       Increment BSlot
'
            For MultiDuplicateCounter = BSlot To UBound(B) - 1
                If CurrentArrayValue = B(MultiDuplicateCounter + 1) Then                                        '           If another match found then ...
                    BSlot = BSlot + 1                                                                           '               Increment BSlot
                End If
            Next
        Else
LastEntry:
            NewArray.Add BSlot                                                                                  '       Save the index value
        End If
    Next
'
MsgBox "Indexes are now loaded into 1D 'NewArray' with a base of 0."

 

[CluelessInVBA]

This code should load the indexes of all unique values found in the array 'B'.

'   Declare Variables
    Dim BSlot                       As Long
    Dim MultiDuplicateCounter       As Long
    Dim CurrentArrayValue           As String
    Dim NewArray                    As Object
'
    Set NewArray = CreateObject("System.Collections.ArrayList")
'
    For BSlot = 1 To UBound(B)
'
        On Error GoTo LastEntry
        If B(BSlot) = B(BSlot + 1) Then                                                                         '   If match found then ...
            NewArray.Add BSlot
            CurrentArrayValue = B(BSlot)                                                                        '       Save the index value
            BSlot = BSlot + 1                                                                                   '       Increment BSlot
'
            For MultiDuplicateCounter = BSlot To UBound(B) - 1
                If CurrentArrayValue = B(MultiDuplicateCounter + 1) Then                                        '           If another match found then ...
                    BSlot = BSlot + 1                                                                           '               Increment BSlot
                End If
            Next
        Else
LastEntry:
            NewArray.Add BSlot                                                                                  '       Save the index value
        End If
    Next
'
MsgBox "Indexes are now loaded into 1D 'NewArray' with a base of 0."

Thanks! Worked like a charm!

 

[johnnyL]

Glad to help @CluelessInVBA.