Filter values that exists in all three columns
This article explains how to extract values that exist in three different columns, they must occur in each of the three columns in order to be extracted. The example demonstrated in this article uses email addresses but you can use any kind of value.
The image above shows three different columns all containing email addresses, here is the question that inspired me to do this article.
This is close to what I need. I have three lists of email addresses. If an email address appears in all three (not two out of three) lists then place it in the duplicate column. Also, I need all three named ranges to be dynamic.
Named ranges
Named ranges allow you to create dynamic cell ranges that automatically grows when new data is added, this way you won't need to adjust cell references in formulas each time new values are added.
There are three columns so we need three different named ranges are necessary, here are the steps to create a named range:
- Go to tab "Formulas" on the ribbon.
- Press with left mouse button on "Name Manager" button.
- Press with left mouse button on "New..." button.
- Name the named range based on the data below.
- Use the first formula below.
- Press with left mouse button on OK button.
- Repeat above steps so in total three named ranges are created, they are Email_Address, Email_Address2, Email_Address3.
Named range: Email_Address
Formula:
The COUNTA function counts the number of cells in column A that are not empty. The INDEX function uses that number to create a cell reference to the last not empty cell.
The named range concatenates the first cell reference and the second cell reference and creates a single cell reference to a cell range. If a new value is added the named range expands automatically, and it shrinks if the last value is deleted.
The disadvantage with this formula is that it doesn't take blank cells into account. If the cell range is not contiguous meaning there are blanks between values than the named range returns a smaller cell reference than required.
This named range formula takes care of that problem:
=Sheet1!$A$2:INDEX($A:$A,MATCH("ZZZZZZZZZZZZZZZZ",$A:$A))
Named range: Email_address2
Formula:
Named range: Email_address3
Formula:
Sheet 2 - Duplicate column
Array formula in cell A2:
How to enter an array formula
- Select cell A2
- Press with left mouse button on in formula bar
- Copy/Paste or type above array formula
- Press and hold Ctrl + Shift
- Press Enter
- The formula is now surrounded by curly brackets {=array_formula}
How to copy array formula
- Select cell A2
- Copy (Ctrl + c)
- Select cell range A3:A8
- Paste (Ctrl + v)
Explaining array formula in cell A2, sheet2
Step 1 - Identify email adresses in Email address 2 column that exists in Email address 1 column
The COUNTIF function counts cells based on a condition or multiple conditions. COUNTIF(range, criteria) In this case the function contains multiple conditions and if the number in the array is larger than 0 (zero) then the value exists in the other column.
COUNTIF(Email_address2, Email_Address)
becomes
COUNTIF({"[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"}, {"[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"})
and returns
{1; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0}
Step 2 - Identify email adresses in Email address 3 column that exists in Email address 1 column
This part compares the first column with the third column.
COUNTIF(Email_address3, Email_Address)
becomes
COUNTIF({"[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"}, {"[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"})
and returns
{1; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Step 3 - Extract only unique distinct email addresse from Email Address 1 column
The NOT function changes booolean value TRU to FALSE and vice versa.
The COUNTIF function makes sure that only one instance of each value is returned.
NOT(COUNTIF($A$1:A1, Email_Address))
becomes
COUNTIF("Emails", {"[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"})
and returns
{TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
Step 4 - Multiply all arrays
This part of the formula applies AND logic between the arrays, this is done by multiplying the arrays.
COUNTIF(Email_address2, Email_Address)*COUNTIF(Email_address3, Email_Address)*NOT(COUNTIF($A$1:A1, Email_Address))
becomes
{1; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0} * {1; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0} * {TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE; TRUE}
and returns
{1; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}
Step 5 - Find relative position of the first email address existing in all three lists
The MATCH function returns the relative position of a specific value in a cell range or array.
MATCH(1, COUNTIF(Email_address2, Email_Address)*COUNTIF(Email_address3, Email_Address)*NOT(COUNTIF($A$1:A1, Email_Address)), 0)
becomes
MATCH(1, {1; 0; 0; 0; 0; 0; 1; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}, 0)
and returns 1.
Step 6 - Return a value of the cell at the intersection of a particular row and column
The INDEX function returns a value from a cell range or aray based on a row and column number.
INDEX(Email_Address, MATCH(1, COUNTIF(Email_address2, Email_Address)*COUNTIF(Email_address3, Email_Address)*NOT(COUNTIF($A$1:A1, Email_Address)), 0))
becomes
INDEX(Email_Address, 1)
becomes
INDEX({"[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"; "[email protected]"}, 1)
and returns [email protected] in cell A2.
Compare category
This article demonstrates ways to extract shared values in different cell ranges, two and three cell ranges. The Excel 365 […]
Array formula in B15: =INDEX($B$3:$B$12, MATCH(0, COUNTIF($B$14:B14, $B$3:$B$12)+IF(((COUNTIF($D$3:$D$11, $B$3:$B$12)>0)+(COUNTIF($F$3:$F$12, $B$3:$B$12)>0))=2, 0, 1), 0)) Copy cell B15 and paste it to […]
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
How would you figure out an unique list where the sum of in one column doesn't match the other column? […]
The image above demonstrates an array formula in cell B11 that extracts values that only exist in List 1 (B3:B7) […]
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
Overview Updating a list using copy/paste is a boring task. This blog article describes how to update values in a price […]
Today I am going to show you how to quickly compare two tables using Conditional Formatting (CF). I am going […]
This article demonstrates a formula that extracts values that exist only in one column out of two columns. There are […]
Question: How do i remove common values between two lists? Answer: The solution in this article, removes common values and […]
Question: i have two sets of data - one has an identifier column and one result column. A2 data1 B2 […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
The formulas above extracts values that exists only in one or the other cell range, if you are looking for […]
In this example we are going to use two lists with identical columns, shown in the image above. It is […]
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
Duplicate values category
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
The following regular formula extracts duplicate values from column B (List1), D (List2) and F (List3) combined, the result is […]
Table of Contents Extract largest duplicate number Extract largest duplicate number - Excel 365 Extract smallest duplicate number Extract smallest […]
The image above demonstrates a formula in cell E3 that extracts duplicate items if they are on the same date. […]
The following regular formula extracts duplicates from column B (List1) and column D (List2) combined, the result is shown in […]
Michael asks: I need to identify the duplicates based on the Columns D:H and put in Column C a small […]
This article demonstrates formulas that extract duplicate values from a column, however, column D contains values that you don't want […]
The following array formula extracts duplicate values sorted from A to Z from cell range B3:B21. Excel array formula in […]
Array formula in D2: =INDEX($A$2:$A$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), ""),ROWS($A$1:A1)), COUNTIF($A$2:$A$21, "<"&$A$2:$A$21), 0)) Array formula in E2: =INDEX($B$2:$B$21, MATCH(SMALL(IF(COUNTIF($B$2:$B$21, $B$2:$B$21)>1, […]
The following image shows you a data set in columns B and C. The formula in cell E2 extracts a […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they contain string specified in cell […]
The array formula in cell B10 extracts duplicate values from cell range B2:D4 if they begin with the condition specified […]
The following array formula in cell B11 extracts duplicates from cell range B3:E8, only one instance of each duplicate is […]
This article demonstrates formulas that extract duplicates based on three conditions. Table of Contents Filter duplicate values using criteria How […]
Question: How do I filter duplicates with a condition? Answer: Column B contains category and column C contains Items. Only […]
Functions in this article
More than 600 Excel formulas
Excel categories
5 Responses to “Filter values that exists in all three columns”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Absolutely outstanding! I struggled with a solution for weeks. Thank you not only for the solution but the explanation of how it all works.
I am not able to do this for some reason... Using Excel 2011 on a Mac.
"Formula refers to empty cells." error...
Vanessa,
Try creating tables instead of dynamic named ranges.
Try creating tables instead of dynamic named ranges.
i love it