Consulting

Results 1 to 6 of 6

Thread: Check Values in Array for Matches

  1. #1
    VBAX Regular
    Joined
    Aug 2016
    Posts
    13
    Location

    Check Values in Array for Matches

    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

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    What should be the result after checking ?

  3. #3
    VBAX Regular
    Joined
    Aug 2016
    Posts
    13
    Location
    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.

  4. #4
    VBAX Regular
    Joined
    Feb 2016
    Posts
    74
    Location
    i am not expert but pseudocode
    check my array sample file and try modify in this link
    http://www.vbaexpress.com/forum/show...Row&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

  5. #5
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  6. #6
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Use a pivottable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •