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 […]
This article shows how to compare two nonadjacent cell ranges and extract values that exist only in one of the […]
This article demonstrates techniques to highlight differences and common values across lists. What's on this page How to highlight differences […]
Duplicate values category
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
Table of Contents How to find the largest duplicate number How to find the largest duplicate number - Excel 365 […]
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