## 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

### Category: Compare

Question: How can I compare two columns to find common values? Array formula in C2: =INDEX($A$2:$A$11, SMALL(IF(COUNTIF($B$2:$B$11, $A$2:$A$11), MATCH(ROW($A$2:$A$11),ROW($A$2:$A$11)), ""), […]

Comments(13) Filed in category: Compare, Excel

This post describes how to compare two price lists in excel 2007. I am going to create three conditional formatting […]

Comments(7) Filed in category: Compare, Excel

This blog post describes how to extract common rows (records) from two lists in excel 2007. Sheet: List 1 Sheet: […]

Comments(7) Filed in category: Compare, Excel, Records

Table of Contents Compare two tables: Remove common rows Compare two tables using a condition Let me demonstrate how to extract […]

Comments(5) Filed in category: Compare, Records

### Category: Unique distinct values

You have quite a few options to choose from if you are looking for a way to create a unique […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values

This post demonstrates how to build an array formula that counts unique distinct values using a criterion or criteria. Tip! […]

Comments(93) Filed in category: Count values, Excel, Unique distinct values

Question: I have two ranges or lists (List1 and List2) from where I would like to extract an unique distinct […]

Comments(78) Filed in category: Excel, Unique distinct values

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]

Comments(53) Filed in category: Excel, Sorted unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Comments(45) Filed in category: Excel, Unique distinct values, VLOOKUP and return multiple values

### 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

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

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