Compare two lists and filter unique values where the sum in one column doesn’t match the other column
List 1
A 10
A 10
B 5
B 6
B 4
List 2
A 10
A 10
B 5
B 6
Answer is B.
Array formula in cell B10:
How to enter an array formula
- Select cell B10
- Type the above formula
- Press and hold CTRL + SHIFT simultaneously
- Press Enter once
- Release all keys
If you did it right, the formula is now encapsulated with curly brackets, like this: {formula}
Don´t enter these characters yourself, they show up automatically.
Explaining the array formula in cell B10
Step 1 - Compare lists
The COUNTIFS function compares column by column between the two lists. If a record is found 1 or more is returned. Nothing found 0 is returned.
COUNTIFS($B$3:$B$7, $E$3:$E$7, $C$3:$C$7, $F$3:$F$7)
returns
{ 1; 1; 1; 1; 0}
Step 2 - Filter only unique distinct values
The COUNTIF function makes sure that only unique distinct values are returned. If a value already exists in the list the function returns 1.
The first argument has a relative and absolute cell reference $B$9:B9. The cell reference expands as you copy and paste the formula further down.
COUNTIF($B$9:B9, $E$3:$E$7)
returns
{0; 0; 0; 0; 0}
This means that no values have been shown yet.
Step 3 -Â Find the relative position
The MATCH function finds the relative position of the first 0 in the array.
MATCH(0, COUNTIFS($B$3:$B$7, $E$3:$E$7, $C$3:$C$7, $F$3:$F$7) + COUNTIF($B$9:B9, $E$3:$E$7), 0)
becomes
MATCH(0, { 1; 1; 1; 1; 0}Â + {0; 0; 0; 0; 0}, 0)
becomes
MATCH(0, { 1; 1; 1; 1; 0} , 0)
and returns 5.
Step 3 - Return values
The INDEX function returns a value in a cell range.
INDEX($E$3:$E$7, 5)
becomes
INDEX({"A"; "A"; "B"; "B"; "B"}, 5)
and returns B in cell B10.
Recommended reading
- Compare two lists of data: Filter records existing in only one list in excel
- Compare two lists of data: Highlight records existing in only one list in excel
- Compare two lists of data: Highlight common records in excel
- Compare two lists of data: Filter common row records in excel
- Compare pricelists in excel 2007
- Quickly compare two tables in excel 2007
If you want to learn more about array formulas join Advanced excel course.
Download excel *.xlsm
Extract shared values between two columns
Question: How can I compare two columns to find values that exists in both cell ranges? The picture above shows […]
Filter common values from three separate columns
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 […]
What values are missing in List 1 that exists i List 2?
Question: How to filter out data from List 1 that is missing in list 2? Answer: This formula is useful […]
Filter shared records from two tables
I will in this blog post demonstrate a formula that extracts common records (shared records) from two data sets in […]
5 easy ways to extract Unique Distinct Values
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Extract a unique distinct list from two columns
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
Vlookup – Return multiple unique distinct values
Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]
Create a unique distinct alphabetically sorted list
The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]
Extract a unique distinct list and sum amounts based on a condition
Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]
5 Responses to “Compare two lists and filter unique values where the sum in one column doesn’t match the other column”
Leave a Reply to ashish mehra
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.
All formulas that belong to you wonderful
Thanks Oscar. Works great. Is there a way to include items that are not in list B in the same formulas. For example C would have avalueof 4 in list A, but it's not in list 2.
Sean,
Thanks Oscar. Works great. Is there a way to include items that are not in list B in the same formulas. For example C would have a value of 4 in list A, but it's not in list 2.
Array formula in cell B10:
=IFERROR(INDEX($E$3:$E$7, MATCH(0, COUNTIFS($B$3:$B$7, $E$3:$E$7, $C$3:$C$7, $F$3:$F$7)+COUNTIF($B$9:B9, $E$3:$E$7), 0)), INDEX($B$3:$B$7, MATCH(0, COUNTIFS($E$3:$E$7, $B$3:$B$7, $F$3:$F$7, $C$3:$C$7)+COUNTIF($B$9:B9, $B$3:$B$7), 0)))
Thanks so much for this invaluable resource, Oscar! I have a question which I don't think is answered by your posts on this topic. Here's an example:
Apples 1
Apples 1
Oranges 2
Apples 3
Apples 3
Bananas 4
Bananas 4
I'm trying to create a formulate that would generate the following list:
Apples
Oranges
Apples
Bananas
That is, I want the formula to use the second column as a criterion for generating the unique list, but the first column as the range for the corresponding values. I hope this is clear, but I'm happy to elaborate if it isn't. Thanks again!
If you want to know more about "Filter a List into unique records using Countif formula", check this link ........
https://www.exceltip.com/excel-filter/filtering-a-list-into-unique-records-using-the-countif-formula.html