Forum Discussion
Comparative Algorithms?
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 need a simple function that just does the following . if a4 - b4 is less than or = to 0 then show NIL . can anyone help please
Philippa Simpson wrote:
I need a simple function that just does the following . if a4 - b4 is less than or = to 0 then show NIL . can anyone help pleaseWhile this is unrelated to this thread use the following to see if it works.
=if(sum(A4-B4)<=0,"NIL",sum(A4-B4))
Hi Peter,
I have an alternative robust approach to solve this problem away from VBA.
This can be done by using Power Query which is also known in Excel 2016 as (Get & Transform Data).
Power Query is much easier than Macros and VBA.
NOTE: If you don't have Excel 2016, you have to download and install Power Query Add-in.
Power Query has a great feature called Merge.
You can make a query from each table, and then merge them together on a specific base.
Please watch this video to learn more about Power Query and its Merge feature.
However, I've done this for you in the attached file, so please open it and try to update tables, then right-click on the merged table and select Refresh to see how it will be updated.
Regards
Thanks so much Haytham!
Peter
As a comment.
Power Query is the best approach, however could be done with formulas. The approach is explained here https://ms-office.wonderhowto.com/how-to/compare-two-lists-and-extract-new-values-excel-332932/
Formula (array one) looks like
=IF(ROWS(A$23:A23) > SUMPRODUCT(--ISNUMBER(MATCH(Table2[First Name]&Table2[Last Name]&Table2[Company],Table1[First Name]&Table1[Last Name]&Table1[Company],0))),"", INDEX(Table2[First Name], SMALL( IF(ISNUMBER(MATCH(Table2[First Name]&Table2[Last Name]&Table2[Company], Table1[First Name]&Table1[Last Name]&Table1[Company],0)), ROW(Table2[First Name])-ROW($G$2)+1), ROWS(A$23:A23) ) ) )and attached.
