Matching Array values 3

[KM8888]

Hi everyone, I had posted this in the VBScript area as it does involve using PCOMM and VBScript but I couldn't find the exact kind of answer I was looking for and this forum has helped me out despite the little differences between my programs so thought I would reach out here as well!


Hopefully I can explain well enough for you experts to understand. Basically I am trying to see if values within two arrays match so it can create a third array that displays those values......... For example

Array()
Array1()

Arrays run in separate For loops kind of like this

For i = 0 to #

Array(i) = .gettext (row, cursor)

i = i+1

Next

Repeat the above step for Array1()

What can I do to see what values that I got from the first array match with the second one? So if the first array has like

Apple
Banana
Coconut
Orange

And the second has

Apple
Strawberry
Orange
Kiwi

Array and Array1 = Orange and Apple

Hopefully I explained that well and it's possible to pull off, any information anybody can provide will surely get a star, I will probably have a follow up question as well. Thanks all


Inappropriate post?
If so, Red Flag it!

 

[macropod]

Try something along the lines of:

Sub Demo()
Dim Arr1(4), Arr2(2), i As Long, j As Long, StrMatch As String
Arr1(0) = "Apple": Arr1(1) = "Pear": Arr1(2) = "Orange": Arr1(3) = "Banana": Arr1(4) = "Mango"
Arr2(0) = "Orange": Arr2(1) = "Banana": Arr2(2) = "Mango"
For i = 0 To UBound(Arr1)
  For j = 0 To UBound(Arr2)
    If Arr1(i) = Arr2(j) Then
      If StrMatch = vbNullString Then
        StrMatch = Arr1(i)
      Else
        StrMatch = StrMatch & " and " & Arr1(i)
      End If
    End If
  Next
Next
MsgBox StrMatch
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[KM8888]

Thanks for this I shall try it out as soon as possible! I might come back with follow ups if I get stuck!

I was just wondering if you could tell me what this part signifies?

If StrMatch = vbNullString Then


the vbNullString isn't something I'm too familiar with. Thanks!

 

[macropod]

I'd have thought vbNullString was self-explanatory - it's the text equivalent of nothing.

Cheers
Paul Edstein
[MS MVP - Word]

 

[KM8888]

Hi again,

So I finally got around to trying the solution and it worked more than what was working before but there was just a couple things.

A big thank you star for anyone with the answer!

It seems that if it detects a match between the arrays it will prompt that match in a message box which is good, but also if there are ones that don't match in the same array it pops up an empty message box because there is also ones that don't match. Is there a way around this? I was researching a bit of things, found some For Each examples but couldn't really apply them, would that be the solution if I only wanted a message box if there was a match, and if there wasn't a match to only prompt a message box if the entire arrays don't match instead of singular values?

Also is there a way I can put those matches into a new array? I know it's a really noob question like my one before but I'm not sure the proper way to do that, like

If StrMatch has matches then

detect how many matches and now StrMatch = StrMatch(3) (or however many matches it detected.

Hopefully I laid those questions out well enough for experts to understand, thanks for any help provided as always. :>

 

[macropod]

If you want to populate another array, you could use code like:

Sub Demo()
Dim Arr1(4), Arr2(2), Arr3(), i As Long, j As Long, k As Long
Arr1(0) = "Apple": Arr1(1) = "Pear": Arr1(2) = "Orange": Arr1(3) = "Banana": Arr1(4) = "Mango"
Arr2(0) = "Orange": Arr2(1) = "Banana": Arr2(2) = "Mango"
k = 0
For i = 0 To UBound(Arr1)
  For j = 0 To UBound(Arr2)
    If Arr1(i) = Arr2(j) Then
      ReDim Preserve Arr3(k)
      Arr3(k) = Arr1(i)
      k = k + 1
    End If
  Next
Next
MsgBox "There were " & k & " matches."
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[KM8888]

Thanks for the response macropod. Was wondering a couple things.

The msgbox with the value "k" will just display a number, how would I display the actual values? I was trying to get it to do so but was having some problems... Also am still having trouble filtering out that if none of the values match then have it not display a pop up box. At the moment with the old coding I get a popup box for the matches but when there are no matches it'll still display a generic empty pop up box.

So I may have had some issues trying to make the new array or something, it seems I am really close but just need to figure out the above scenario. Any help always appreciated!

Thank you!

 

[KM8888]

Sorry, I think I may have figured out that last question but I have a new one...

Basically say I want the new array to only gather 1 match and not store it a second time. So if

ARRAY1= Orange, Orange, Banana, Mango
ARRAY2= Orange, Orange, Banana, Mango

ARRAY3 WITH MATCHES = Orange, Banana, Mango.

Is this doable? Thanks again

 

[MajP]

...
    If Arr1(i) = Arr2(j) Then
      if not ExistsInArray(Arr3,Arr1(i)) then
         ReDim Preserve Arr3(k)
         Arr3(k) = Arr1(i)
         k = k + 1
      end if 
    End If
...

Public Function ExistsInArray(arr As Variant, criteria As Variant) As Boolean
  Dim i As Integer
  For i = LBound(arr) To UBound(arr)
    If arr(i) = criteria Then
      ExistsInArray = True
      Exit Function
    End If
  Next i
End Function

 

[KM8888]

Not sure if I'm applying it incorrectly or it's just my inexperience that I couldn't really get it to work :S. I'll check back later if I can... Thank you though! Any other ideas to simplify it for my simple mind are welcome!

 

[MajP]

Sorry that code probably gave you an error the first time through. You need to check that a dynamic area is allocated prior to calling the ubound or lbound. This should work.

Sub Demo()

Dim Arr1(4), Arr2(4), Arr3(), i As Long, j As Long, k As Long
Dim blnArrayAllocated As Boolean

Arr1(0) = "Apple": Arr1(1) = "Pear": Arr1(2) = "Orange": Arr1(3) = "Banana": Arr1(4) = "Mango"
Arr2(0) = "Orange": Arr2(1) = "Banana": Arr2(2) = "Mango": Arr2(3) = "Orange": Arr2(4) = "Mango"
k = 0

For i = 0 To UBound(Arr1)
  For j = 0 To UBound(Arr2)
    If Arr1(i) = Arr2(j) Then
      If Not blnArrayAllocated Then
        blnArrayAllocated = True
        ReDim Preserve Arr3(k)
        Arr3(k) = Arr1(i)
        k = k + 1
      ElseIf Not ExistsInArray(Arr3, Arr1(i)) Then
        ReDim Preserve Arr3(k)
        Arr3(k) = Arr1(i)
        k = k + 1
      End If
    End If
  Next
Next
MsgBox "There were " & k & " matches."

For k = LBound(Arr3) To UBound(Arr3)
  Debug.Print Arr3(k)
Next k
End Sub

Public Function ExistsInArray(arr As Variant, criteria As Variant) As Boolean
  Dim i As Integer
  For i = LBound(arr) To UBound(arr)
    If arr(i) = criteria Then
      ExistsInArray = True
      Exit Function
    End If
  Next i
End Function

results

Orange
Banana
Mango

 

[macropod]

The first lot of code I posted showed how to display the results, the second lot showed how to populate another array with those same results. The simplest way to have only unique entries in the new array is to test whether they're already in the output string:

Sub Demo()
Dim Arr1(4), Arr2(2), Arr3(), i As Long, j As Long, k As Long, StrMatch As String
Arr1(0) = "Apple": Arr1(1) = "Pear": Arr1(2) = "Orange": Arr1(3) = "Banana": Arr1(4) = "Mango"
Arr2(0) = "Orange": Arr2(1) = "Banana": Arr2(2) = "Mango"
k = 0
StrMatch = vbCr
For i = 0 To UBound(Arr1)
  For j = 0 To UBound(Arr2)
    If Arr1(i) = Arr2(j) Then
      If InStr(StrMatch, vbCr & Arr1(i) & vbCr) = 0 Then
        StrMatch = StrMatch & Arr1(i) & vbCr
        ReDim Preserve Arr3(k)
        Arr3(k) = Arr1(i)
        k = k + 1
      End If
    End If
  Next
Next
MsgBox "There were " & k & " matches:" & StrMatch
End Sub

Cheers
Paul Edstein
[MS MVP - Word]

 

[KM8888]

Hey all again,

Thanks so much for the help so far, have another addition I'd like to see if I can do.

Basically is there a simple way to match up 1 value to another value with an additional number on the end.

Basically I have this new array of matches but I'd also like to add some values to it when for example there is something like this...

Orange exists in 1 array

In another set of array Orange exists again but it's full text is "Orange01"

Would I be able to do something like if Orange matches Orange + 01 on the end of it then

Redim arr3(k)
Add Orange01 to arr3(k)

While still keeping all my previous matches as well?


Thanks, hopefully that explanation isn't too horrid, any help is always noted

 

[SkipVought]

for i = 0 to ubound(ary1)
  for j = 0 to ubound(ary2)
    if ucase(ary1(i)) like ucase(ary2(j)) & "*" then
       'what you do when orange like orange01

    end if
  next
next

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[macropod]

Hi KM888,

It would be helpful if you said at the outset what it is you're trying to achieve. Re-writing the code each time you decide to add a new requirement isn't exactly rewarding.

As for your latest requirement, a couple of things should be noted:
1. Every 'output' value exists in all three arrays and, based on what you've posted before, some may exist multiple times in arrays 1 & 2. Accordingly, output like 'Orange exists in 1 array' is fairly meaningless, unless: (a) what you're really after is those cases where a value appears in array 1 or array 2, but not both; or (b) you want to count how many matches exist in a given array (and then you'll probably need to differentiate which array(s) the multiple instances are found in).
2. If you want to have display text like 'Orange exists in 1 array' and 'Orange01', you can do that by appending the additional text to the output, without disturbing the array contents at all. Another way, if you're committed to storing those values in one of the arrays is to add one or more extra dimensions to that array and store the variants in the different array dimensions.

Cheers
Paul Edstein
[MS MVP - Word]

 

[KM8888]

Yes, I apologize I wish I was more adept in general or could lay out things better. I'll try to expand fully, my last description I realized probably wasn't very good for what I am trying to do.

Basically I am trying to match these values because on another screen these values exist again, so on this new screen I only want to look for the matching values, then basically grab text that goes along with those matches.

For example one page has like "fruits you have entered manually" and another section on the same page has a list of fruits that is like "These fruits have certain descriptions to take note of" And to see the text for these descriptions you have to go to a different page so I only want to see the text for the descriptions of matched values between what is entered manually and those special descriptions.

So my last question I did single out orange but basically I would want to match up any of those special values that have the text fruit01 so if grape and orange are entered it can identify if that text exists + the number 01 and then add that to the matching array.

After I have all the matches I am looking for I switch to a new page and then start doing "locate arry1(0) text and then grab text the description for that text and store it" then do that for (1) (2) etc...

So I am trying to get to that goal, I hope it helps a little more, thank you all again for the help.

Skip, what you offered there, is the & "*" part basically if I put "01" in place of that it will match them up?

Thank you both as always.

 

[KM8888]

And sorry I just realized you have to confirm stars when clicking to give one, must not have given them out properly before, tried to catch up.

 

[SkipVought]

I thought that you had ORANGE in one array and ORANGE01 in the other, for instance.

You tell me how you want to match them up?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[KM8888]

Yeah, thats correct Skip, except there are instances where Orange01 won't exist and the "01" can apply to any of the fruits. So instead of trying each fruit and seeing if that fruit and 01 exist I would want to do something like...

Does any value in arry(1) = any value in arry(2) + the text 01 on the end of it.

Does that help? Thank you for your help

 

[SkipVought]

so how about

    if ucase(ary1(i)) = left(ucase(ary2(j)), len(ary1(i)) then

assuming that ary1 contains 'orange' and ary2 contains 'orange01'???

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]