PDA

View Full Version : [SOLVED] Excel VBA -Comparing Two arrays



Dennis2017
02-04-2017, 07:51 AM
Hi,

I have two arrays. How do I cross check using a simple array (row 4) against a multidimensional array (row 8 to 17) with result display in two columns in C21 to D25 as shown in the picture?

Thanks in advance.

SamT
02-04-2017, 10:02 AM
I don't see the correlation between the result and the data.



for i = 1 to Ubound Array1
For j = 1 to UBount Array2
For k = 1 to UBound Aray2(1)

If Array1(i) = Array2(j, k) Then Match

Next
Next
Next

mancubus
02-04-2017, 04:08 PM
one approach may be:

run below code when the sheet is selected.


Sub vbax_58483_compare_two_arrays()

Dim arr1, arr2
Dim i As Long, j As Long
Dim matches As String

arr1 = Range("C4:F4").Value
arr2 = Range("C8:F17").Value

For i = LBound(arr2, 1) To UBound(arr2, 1)
matches = ""
For j = LBound(arr1, 2) To UBound(arr1, 2)
If UBound(Filter(Application.Index(arr2, i, 0), arr1(1, j))) > -1 Then matches = matches & " & " & arr1(1, j)
Next j
If matches = "" Then matches = String(4, " ")
Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Mid(matches, 4)
matched = False
Next i

End Sub

Dennis2017
02-05-2017, 01:41 AM
Hi mancubus,

Thank you for your reply. The two arrays works ok, however the displayed result is on the same column i.e. Column A. How do I make it into two column as shown in the picture?

SamT
02-05-2017, 10:25 AM
How do I make it into two column as shown in the picture?
That's kinda hard, since the two columns of results shown in the picture have no apparent relation to the data shown in the picture.

There is not a singe row in the data with 2 dogs, nor is there one with fish, cat, and chicken.

There are no columns of data with just Dog, but none of the others, however, there is a column with all four.

How do you come up with a blank cell in the results?

Dennis2017
02-05-2017, 09:23 PM
The blank cell means the source array and reference array have nothing in common.

mancubus
02-06-2017, 12:38 AM
upload your workbook so we can see where you want the results to be displayed.

actually i dont understand why it is important to put the results in two (maybe 3, 4, in the future?) columns...

Dennis2017
02-06-2017, 05:47 AM
I have originally substitute numbers with fish and seahorse for simplicity. The data type is alphanumeric.
Regarding the number of column, it depends on the list box selection, text box input, combo box etc. For example, if user selects the number 4 from the list box, this represent 4 columns. The data will be displayed in 4 columns. Hope this helps.

mancubus
02-06-2017, 07:08 AM
Sub vbax_58483_compare_two_arrays()

Dim arr1, arr2
Dim i As Long, j As Long, r As Long, c As Long
Dim numrow As Long, numcol As Long
Dim matches As String

arr1 = Range("C4:F4").Value
arr2 = Range("C8:F17").Value
r = 0
c = 0
numcol = Application.InputBox(Prompt:="Enter the number of the columns to display the matches!", Type:=1)
If numcol = False Then MsgBox "Cancelled": Exit Sub
numrow = Application.RoundUp(UBound(arr2, 1) / numcol, 0)

For i = LBound(arr2, 1) To UBound(arr2, 1)
matches = ""
For j = LBound(arr1, 2) To UBound(arr1, 2)
If UBound(Filter(Application.Index(arr2, i, 0), arr1(1, j))) > -1 Then matches = matches & " & " & arr1(1, j)
Next j
If matches = "" Then matches = String(4, " ")
Range("C21").Offset(r, c).Value = Mid(matches, 4)
r = r + 1
If r = numrow Then
r = 0
c = c + 1
End If
Next i

End Sub

Dennis2017
02-08-2017, 04:05 AM
Thanks mancubus.