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
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
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