PDA

View Full Version : Solved: Comparing elements in 2 arrays



Digita
03-17-2009, 10:33 PM
Hi guys,

I got stuck in a problem which initially sounds simple but is driving me nuts. Basically, I would like to count the occurences of all unique elements in an array which consists of 163, 357, 163, 357, 163, 357, 323. I put the unique numbers in a kinda of helper array and loop all the numbers in the original array through it and count the ones that are the same.

Here is my script and the part that does not work is marked in red.

abc = Array(163, 357, 163, 357, 163, 357, 323)
For Each cel In abc
If unik = Empty Then
unik = cel
ElseIf InStr(1, unik, cel, 1) = 0 Then unik = unik & "," & cel
End If
Next
arr = Split(unik, ",")
For j = 0 To UBound(arr, 1)
For i = 0 To UBound(abc, 1)
Debug.Print arr(j) & " - " & abc(i) & " - " & IIf(arr(j) = abc(i), "Equal", "")
If arr(j) = abc(i) Then iCnt = iCnt + 1

Next i

iCnt = 0 'Reset counter

Next j

Thanks in advance for your generous help.

Regards


kp

Bob Phillips
03-18-2009, 02:16 AM
Dim cel As Variant
Dim abc As Variant
Dim unik As Variant
Dim arr As Variant
Dim iCnt As Long
Dim i As Long, j As Long

abc = Array(163, 357, 163, 357, 163, 357, 323)
For Each cel In abc

If unik = Empty Then

unik = cel
ElseIf InStr(1, unik, cel, 1) = 0 Then

unik = unik & "," & cel
End If
Next

arr = Split(unik, ",")
For j = 0 To UBound(arr, 1)

For i = 0 To UBound(abc, 1)

Debug.Print arr(j) & " - " & abc(i) & " - " & IIf(Val(arr(j)) = abc(i), "Equal", "")
If Val(arr(j)) = abc(i) Then iCnt = iCnt + 1
Next i

iCnt = 0 'Reset counter
Next j


But I cannot see what iCnt is used for.

Bob Phillips
03-18-2009, 02:20 AM
Another way



Dim cel As Variant
Dim abc As Variant
Dim coll As Collection

abc = Array(163, 357, 163, 357, 163, 357, 323)
Set coll = New Collection
On Error Resume Next
For Each cel In abc

coll.Add cel, CStr(cel)
Next
On Error GoTo 0

For Each cel In coll

Debug.Print "Duplicate: " & cel
Next cel

Debug.Print "Num duplicates: " & coll.Count
Set coll = Nothing

Digita
03-18-2009, 04:42 AM
Hi Bob,

You rock. Another problem solved. Yes, I forgot to wrap the unique elements in the helper array with Val function.




But I cannot see what iCnt is used for.
You are right. The purpose of the iCnt is unclear in the code I posted. Since I had a problem in the earlier part of the code, it was my oversight in omitting a line to put this variable into good use. So here is my amended code.

Dim cel As Variant
Dim abc As Variant
Dim unik As Variant
Dim arr As Variant
Dim iCnt As Long
Dim i As Long, j As Long

abc = Array(163, 357, 163, 357, 163, 357, 323)
For Each cel In abc

If unik = Empty Then

unik = cel
ElseIf InStr(1, unik, cel, 1) = 0 Then

unik = unik & "," & cel
End If
Next

arr = Split(unik, ",")
For j = 0 To UBound(arr, 1)

For i = 0 To UBound(abc, 1)

Debug.Print arr(j) & " - " & abc(i) & " - " & IIf(Val(arr(j)) = abc(i), "Equal", "")
If Val(arr(j)) = abc(i) Then iCnt = iCnt + 1

Next i
Debug.Print arr(j) & " - " & iCnt
iCnt = 0 'Reset counter
Next j

Thanks a bunch Bob for your time and expert advice.

Best regards


kp

PS - I love your alternate code with the collection method as well. It is much shorter. I'll keep it handy for future reference. Thanks again.

mdmackillop
03-18-2009, 04:21 PM
Another way


Debug.Print "Duplicate: " & cel
Next cel
Debug.Print "Num duplicates: " & coll.Count
Set coll = Nothing



Application.Replace "Duplicate", "Unique"

:devil2:

Digita
03-18-2009, 05:49 PM
Hi Mal,

Right. I'm sure Bob was meant to say "unique" :-).

Cheers


kp