Compare 2 Arrays

Hi guys

I've always been able to figure out what I need to do in Excel, but this one has got me stumped. Basically, I have two arrays, and I need to return back the common values. For example:

{0, 0, 0, 4, 0, 0, 7, 8, 9}
{1, 0, 0, 4, 0, 0, 0, 0, 9}

In this example, 4 and 9 are the common values. Yes they're also the same index and that can be optional for me (ie if there's a way to do it when they're not the same index then it's not a problem too). I need to be able to return and display something like "4 9" to a cell.

How to do this?

Thanks guys

There may be easier methods, but I'd do something like this:

I'm filling the arrays with your values; you can fill them however you want to the key is the comparisons in the loop, notice the string will not get written to C if it's not equal or if it's 0.

Sub CompareArrays()
Dim A(0 To 8) As Integer
Dim B(0 To 8) As Integer
Dim C As String
Dim X As Integer
Dim Y As Integer

A(0) = 0
A(1) = 0
A(2) = 0
A(3) = 4
A(4) = 0
A(5) = 0
A(6) = 7
A(7) = 8
A(8) = 9

'{0, 0, 0, 4, 0, 0, 7, 8, 9}

B(0) = 1
B(1) = 0
B(2) = 0
B(3) = 4
B(4) = 0
B(5) = 0
B(6) = 0
B(7) = 0
B(8) = 9

'{1, 0, 0, 4, 0, 0, 0, 0, 9}

For X = 0 To 8
    For Y = 0 To 8
        If A(X) = B(Y) And A(X) <> 0 Then
            C = C & " " & A(X)
        End If
    Next Y
Next X

MsgBox C

End Sub

OK, thanks. So there's no built in function... poop

You'll have to forgive me, but VB scripting isn't my strong point. I can get what code you have written to create a little pop up window with the result, but I can't get it to enter it in to a cell. I know I'm doing something wrong, but I don't know what.

Also, what would be the best ways of passing the arrays to the function? Individually or each array at a time?

Thanks again

where are the arrays coming from?
where are they in your worksheet?
where do you want the result to go?
will it always go to the same cell location?

For X = 0 To 8
    For Y = 0 To 8
        If A(X) = B(Y) And A(X) <> 0 Then
            C = C & " " & A(X)
        End If
    Next Y
Next X
'To put the result in a cell do this:

Range("A1").value = C

MsgBox C

There are many arrays in a table. I'm basically experimenting with using Excel to solve some puzzles. I'm wondering if I'd have been better off doing it with PHP since that's my forte.

Basically, there are 9 arrays in L2:T2 through L10:T10 (stacked on top of each other) and another 9 in B12:B20 through J12:J20, alongside each other. Where they all meet (ie between B2 and J10) is where I want the comparison doing.

Does that help?

Sounds like they are basically cell values that you are calling an array?
You could pass the ranges to the sub and use the ranges as the arrays.

You may want to post an example workbook.

You may also want to look at the Intersect method

http://msdn.microsoft.com/en-us/library/bb209961.aspx

Ooo, that's interesting, that might be what I'm looking for... give me a mo and I'll get back to you, thanks