Hey y'all. I've got two very large, different sized arrays that I need to find common elements between. For example, consider that I have the two following, arbitrary, made-up arrays:
I have common names between the two arrays, and I have non-common names. I also have different amounts of columns in each array. What I want to do, is to compare the name columns in each array (i.e. A,B for Array 1 and G,I for Array 2), and if the name from Array 1 is also somewhere in Array 2, I'd like a way of knowing that, be it an indicator column, etc. After sorting, I'd like the final Array to look like this:
as these are the only two common rows between the two arrays. I'll be honest, I don't really know how to accomplish this with Excel functions, as all comparative functions that I would use (MATCH, etc.) only use one input, instead of 2 (first and last name). I tried using some VBA code, but I've only ever used Matlab so my abilities are somewhat lackluster. Here's the code I tried using:
Dim i As Integer
Dim j As Integer
For i = 2 To 4324
For j = 2 To 3218
If Worksheets(3).Range("B" & i, "C" & i) = Worksheets(3).Range("G" & j, "I" & j) Then
Worksheets(3).Range("V" & j).Value = Worksheets(3).Range("V" & j).Value + 1
Else
Worksheets(3).Range("V" & j).Value = Worksheets(3).Range("V" & j).Value + 0
End If
Next j
Next i
Essentially, I use two for loops - j will iterate over the Array 2 names, and i will iterate over the Array 1 names. The idea was to say that if Cell Bi and Ci (first and last name) equal cells Gj and Ij for a given value of i and j, then the corresponding cell in the column next to Array 2 (my indicator column) will increase by 1. If it doesn't, the value remains the same. This way, I'll have an indicator column of 1's or 0's, and can the use sort to narrow down Array 2 to the names I want. However, when I run the script, I get a Run Time Error '13' Type mismatch.
Would anyone be able to suggest a fix to my code or an alternate approach to this problem?
I don't think you need code to accomplish your task. You can use this solution:
Let me know if you would prefer code. It's probably not so bad to do it that way either.
Peter-
Not sure you can do what you want all in one formula but I've used a two and a few helper columns in order to accomplish the task. You'll need to adjust the formulas based on your data structure.
1. An Array Formula to get unique values in each list
2. A simple Vlookup
I'm going to attach an example file as I think it may be easier to comprehend. When entering the array formula use key combination CTRL + SHIFT + ENTER then drag down.... When using the vlookup you can simple put this formula in cell B13 and then drag to the right and then down....