# Compare tables: Filter records occurring only in one table

#### Table of Contents

## 1. Compare tables: Filter records occurring only in one table

In this example we are going to use two lists with identical columns, shown in the image above. It is easy to modify the COUNTIFS function if your columns are not arranged in the same order, see the COUNTIFS explanationÂ below.

### 1.1 Create named ranges

There is really no need for named ranges in this example, although they shorten array formulas considerably. You can skip this step if you want.

- Select A2:A13 on sheet "List 2"
- Type Year in Name Box
- Press Enter

Repeat with remaining ranges:

Sheet: List 2 , Range:B2:B13, Name: Asset

Sheet: List 2 , Range:C2:C13, Name: Cost

Sheet: List 1 , Range:A2:A11, Name: Year1

Sheet: List 1 , Range:B2:B11, Name: Asset1

Sheet: List 1 , Range:C2:C11, Name: Cost1

### 1.2 Filter records existing in only one list

**Array formula in cell A4:**

To enter an array formula, type the formula in a cell then press and hold CTRL + SHIFT simultaneously, now press Enter once. Release all keys.

The formula bar now shows the formula with aÂ beginning and ending curly bracket telling you that you entered the formula successfully. Don't enter the curly brackets yourself.

Copy cell A4 and paste it to the right as far as needed. Copy cells and paste down as far as needed.

**Array formula in cell A14:**

Copy cell A4 and paste it to the right as far as needed. Copy cells and paste down as far as needed.

### 1.3 Explaining the array formula in cell A4

*Step 1 - Find records existing only in List 1*

Let us take a look at the bolded part of the formula.

=INDEX('List 1'!$A$2:$C$11, SMALL(IF(**COUNTIFS(Year, Â Year1, Â Asset, Â Asset1, Â Cost, Â Cost1)=0**, ROW(Year1)-MIN(ROW(Year1))+1), ROW(A1)), COLUMN(A1))

*criteria_range1,criteria1, criteria_range2, criteria2...*)

COUNTIFS(Year, Â Year1, Â Asset, Â Asset1, Â Cost, Â Cost1)

becomes

COUNTIFS({1997, 1998, ... , 1577})

and returns this array: {1, 1, 1, 0, 1, 1, 1, 1, 0, 0}

COUNTIFS(Year, Â Year1, Â Asset, Â Asset1, Â Cost, Â Cost1)=0

becomes

{1, 1, 1, 0, 1, 1, 1, 1, 0, 0}=0

and returns

{FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}

*Step 2 - Convert boolean array to row numbers*

TheÂ IF functionÂ has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(COUNTIFS(Year, Â Year1, Â Asset, Â Asset1, Â Cost, Â Cost1)=0, ROW(Year1)-MIN(ROW(Year1))+1)

becomes

IF({FALSE, FALSE, FALSE, TRUE, FALSE, FALSE, FALSE, FALSE, TRUE, TRUE}, {1, 2, 3, 4, 5, 6, 7, 8, 9, 10})

and returns

{FALSE, FALSE, FALSE, 4, FALSE, FALSE, FALSE, FALSE, 9, 10}

*Step 3 - **Return the k-th smallest value*

To be able to return a new value in a cell each I use theÂ SMALL functionÂ to filter column numbers from smallest to largest.

TheÂ ROW functionÂ keeps track of the numbers based on a relative cell reference. It will change as the formula is copied to the cells below.

SMALL(IF(COUNTIFS(Year, Â Year1, Â Asset, Â Asset1, Â Cost, Â Cost1)=0, ROW(Year1)-MIN(ROW(Year1))+1), ROW(A1))

becomes

SMALL({FALSE, FALSE, FALSE, 4, FALSE, FALSE, FALSE, FALSE, 9, 10}, 1)

and returns 4.

*Step 4 - Return a value of the cell at the intersection of a particular row and column*

TheÂ INDEX functionÂ returns a value based on a cell reference and column/row numbers.

INDEX('List 1'!$A$2:$C$11, SMALL(IF(COUNTIFS(Year, Â Year1, Â Asset, Â Asset1, Â Cost, Â Cost1)=0, ROW(Year1)-MIN(ROW(Year1))+1), ROW(A1)), COLUMN(A1))

becomes

=INDEX('List 1'!$A$2:$C$11, 4, COLUMN(A1))

becomes

=INDEX('List 1'!$A$2:$C$11, 4, 1)

and returns 1999.

## 2. Compare two lists and filter unique values where the sum in one column doesn't match the other column

How would you figure out an unique list where the sum of in one column doesn't match the other column? I'm comparing list 1 to list 2

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:

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

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

### Get the Excel file

Compare-two-lists-and-filter-unique-values-where-the-sum-in-one-column-doesn%E2%80%99t-match-the-other-column.xlsx

### 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 […]

### Records category

This article demonstrates how to filter records occurring in only one out of two Excel defined tables. It also shows […]

This article demonstrates how to sort records in a data set based on their count meaning the formula counts each […]

### Excel categories

### 5 Responses to “Compare tables: Filter records occurring only in one table”

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

**Contact Oscar**

You can contact me through this contact form

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