Forum Discussion

Peter Scott Keillor's avatar
Peter Scott Keillor
Copper Contributor
May 02, 2018

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 hav...
Formulas & Functions
Need Help
Haytham Amairah's avatar
Haytham Amairah
Silver Contributor
May 02, 2018

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

Peter Scott Keillor's avatar
Peter Scott Keillor
Copper Contributor
May 02, 2018

Thanks so much Haytham!

 

Peter

  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    May 02, 2018

    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.

Resources