PDA

View Full Version : Check Values in Array for Matches



LePig
02-21-2018, 05:26 AM
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
02-21-2018, 05:28 AM
What should be the result after checking ?

LePig
02-21-2018, 05:34 AM
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
02-21-2018, 07:31 AM
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
02-21-2018, 07:41 AM
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
02-21-2018, 08:16 AM
Use a pivottable.