Check Values in Array for Matches

[LePig]

Hi,

I have an array for example

arr(1) - mackeral
arr(2) - Tuna
arr(3) - Salmon
arr(4) - Salmon
arr(5) - Cod
arr(6) - mackeral
arr(7) - Tuna
arr(8) - Cod
arr(9) - Salmon
arr(10) - Cod

I need to check the arr values for any matches.

So for example check arr(1) against arr(2) to arr(10) and so on until all are checked against each other.

Any pointers would be greatly appreciated.

Kind Regards

Matt

[snb]

What should be the result after checking ?

[LePig]

For example if checking

arr(1) - Mackerel
It would return the number 6 as the match

If checking arr(2) - Tuna
It would return the number 7 as the match

I want it to return the numbers as the matches.

From this information i want to get average prices.

[xman2000]

i am not expert but pseudocode
check my array sample file and try modify in this link
http://www.vbaexpress.com/forum/showthread.php?61076-Array-Filtered-without-Spaces-after-1aRow&highlight=
"http://www.vbaexpress.com/forum/showthread.php?61076-Array-Filtered-without-Spaces-after-1aRow&highlight="



For counterX = 1 to 10
for CounterY = 1 to 10
if arr(counterX) = arr(counterY) then
ArrReturn(conterX ) = CounterY
next
next

[Paul_Hossler]

Why not ...




Option Explicit

Sub test()

Dim arr(1 To 10) As String
Dim i As Long, j As Long

arr(1) = "mackeral"
arr(2) = "Tuna"
arr(3) = "Salmon"
arr(4) = "Salmon"
arr(5) = "Cod"
arr(6) = "mackeral"
arr(7) = "Tuna"
arr(8) = "Cod"
arr(9) = "Salmon"
arr(10) = "Cod"

For i = LBound(arr) To UBound(arr) - 1
For j = i + 1 To UBound(arr)
If arr(i) = arr(j) Then
MsgBox i & " = " & j & " = " & arr(j)
End If
Next j
Next i

End Sub




There are ways to do this faster, but this is the simplest approach


Looking at the stated final objective, you could use AVERAGEIF() function

[snb]

Use a pivottable.