Contents
The topic of comparing the two lists has already been raised repeatedly and from different sides, but remains one of the most relevant everywhere and always. Let’s look at one of its aspects – counting the number and displaying the matching values in two lists. Let’s say we have two ranges of data that we want to compare:
For convenience, you can give them names so that you can later use them in formulas and references. To do this, select cells with list items and on the tab formula click the button Name Manager – Create (Formulas — Name Manager — Create). You can also turn tables into “smart” ones using keyboard shortcuts Ctrl+T or buttons Format as a table tab Home (Home — Format as Table).
Counting the number of matches
To count the number of matches in two lists, you can use the following elegant formula:
In the English version it will be =SUMPRODUCT(COUNTIF(List1;List2))
Let’s take a closer look at it, because a couple of non-obvious chips are hidden in it.
First, the COUNTIF function (COUNTIF). It typically counts the number of lookup values in a range of cells and is used in the following configuration:
=COUNTIF(Where to looking for; What to look for)
Typically, the first argument is a range, and the second is a cell, value, or condition (one!) that we are looking for matches in the range. In our formula, the second argument is also a range. In practice, this means that we force Excel to go through all the cells in the second list in turn and count the number of occurrences of each of them in the first list. In fact, this is equivalent to a whole column of additional calculations, folded into one formula:
Second, the function SUMPRODUCT (SUMPRODUCT) performs two functions here – sums up the calculated COUNTIF matches and at the same time turns our formula into an array formula without having to press a keyboard shortcut Ctrl+Shift+Enter. The array formula is necessary for the COUNTIF function in the mode with two range arguments to work correctly.
Listing matches with an array formula
If you need to not only count the number of matches, but also display the matching elements in a separate list, then you will need a not the simplest array formula:
In the English version it will be, respectively:
=INDEX(Список1;MATCH(1;COUNTIF(Список2;Список1)*NOT(COUNTIF($E$1:E1;Список1));0))
The logic behind this formula is as follows:
- the COUNTIF fragment(List2;List1), as in the example before, looks for matches of elements from the first list in the second
- snippet NOT(COUNTIF($E$1:E1;List1)) checks if the current match above has already been found
- and finally the INDEX and MATCH function chain retrieves the matched element
Do not forget to press the keyboard shortcut at the end of entering this formula Ctrl+Shift+Enter, because it must be entered as an array formula.
#N/A errors that occur on redundant cells can be additionally intercepted and replaced with spaces or empty strings “” using the function IFERROR (IFERROR).
Listing matches with Power Query Query Merge
On large tables, the array formula from the previous method can slow down quite noticeably, so it will be much more convenient to use Power Query. This is a free add-on from Microsoft that can load into Excel 2010-2013 and transform almost any data. The power and capabilities of Power Query are so great that Microsoft has included all of its features by default in Excel since the 2016 version.
First, we need to load our tables into Power Query. To do this, select the first list and on the tab Data (in Excel 2016) or tab Power Query (if it was installed as a separate add-in in Excel 2010-2013) click the button From Table/Range (From Table):
Excel will turn our table into a “smart” one and give it a generic name Table 1. After that, the data will fall into the Power Query query editor. We do not need to do any transformations with the table, so you can safely press the button in the upper left corner Close and load – Close and load in… (Close & Load To…) and select in the window that appears Just create a connection (Create only connection):
Then we repeat the same with the second range.
And finally, we move on to identifying matches. To do this, on the Data tab or on the Power Query tab, we find the command Get Data – Combine Requests – Combine (Get Data — Merge Queries — Merge):
In the window that opens, we do three things:
- select our tables from the drop down lists
- select columns for comparison
- Choose Connection type = Internal (Inner Join)
After clicking on OK only matching lines will be displayed on the screen:
Unnecessary column Table 2 you can right-click to delete, and rename the title of the first column to something more understandable (for example Matches). And then unload the resulting table onto a sheet using the same command close and download (Close & Load):
If the values in the source tables change in the future, then you must remember to update the resulting list of matches with the right mouse button or keyboard shortcut Ctrl+Alt+F5.
Macro to list matches
Of course, to solve the problem of finding matches, you can also use a macro. To do this, click the button Visual Basic tab developer (Developer). If it is not visible, then you can display it through File – Options – Ribbon Setup (File — Options — Customize Ribbon).
In the Visual Basic editor window, you need to add a new empty module through the menu Insert – Module and then copy our macro code there:
Sub Find_Matches_In_Two_Lists() Dim coll As New Collection Dim rng1 As Range, rng2 As Range, rngOut As Range Dim i As Long, j As Long, k As Long Set rng1 = Selection.Areas(1) Set rng2 = Selection.Areas(2 ) Set rngOut = Application.InputBox(Prompt:="Select the cell from which you want to output matches", Type:=8) 'Load the first range into the collection For i = 1 To rng1.Cells.Count coll.Add rng1.Cells (i), CStr(rng1.Cells(i)) Next i 'check if the second range elements are in the collection k = 0 On Error Resume Next For j = 1 To rng2.Cells.Count Err.Clear elem = coll.Item(CStr (rng2.Cells(j))) If CLng(Err.Number) = 0 Then 'if a match is found, then output with a shift down rngOut.Offset(k, 0) = rng2.Cells(j) k = k + 1 End If Next j End Sub
Using the added macro is very simple. Highlight by holding down the key Ctrl, both ranges and run the macro with the Macros button on the tab developer (Developer) or keyboard shortcut Alt+F8. The macro will ask you to specify a cell, starting from which you want to display a list of matches and after clicking on OK will do all the work:
A more advanced macro of this type is, by the way, in my PLEX add-in for Microsoft Excel.
- Finding differences in two Excel lists
- Merging two lists without duplicates (3 ways)
- What are macros, how to use them, where to copy macro code in Visual Basic