## Advanced Excel Formulas

Basic Formulas | Advanced Formula

#### Table of Contents

- How do I compare two different lists?
- How do I compare two different data sets?
- How can I extract values shared/not shared between two cell ranges?
- How can I automatically plot date ranges in a schedule?
- How can I find overlapping date ranges?
- How can I identify overlapping time ranges?
- How can I automatically create date ranges?
- How do I combine or merge two columns?
- How do I combine or merge three columns?
- How do I combine or merge two tables?
- How do I count a specific pattern in a cell value?
- How do I count unique distinct values?
- How do I count unique values?
- How do I count duplicate values?
- How do I count cells containing text from list?
- How do I sort values using a formula?
- How do I count repetead contiguous values
- How do I extract unique distinct values?
- How do I extract duplicate values?
- How do I extract unique distinct records?
- How do I extract duplicate records?

### How do I compare two different lists?

The following articles demonstrate how to extract values that either is shared by both columns or not:

How to extract not shared values in two columns

Extract shared values between two columns

What values are missing in List 1 that exists i List 2?

Compare two columns and return differences

Compare two columns in different worksheets

### How do I compare two different data sets?

A dataset is a group of related values, often each row contains values that form a record. Data organized vertically is not that common.

How to extract differences between price lists

Filter records occurring only in one table

Filter shared records from two tables

### How can I extract values shared/not shared between two cell ranges?

The cell range in the articles below contains values that are NOT organized by row, in other words, they are not related.

Filter common values between two ranges

Filter values occurring in range 1 but not in range 2

### How can I automatically plot date ranges in a schedule?

This article Shift Schedule explains how to show date ranges in a schedule using formulas. You can also do the opposite, Get date ranges from a schedule using a formula.

The following article demonstrates a calendar that highlights events: Yet another excel calendar or: Highlight events in a calendar I recommend that you check out the Calendar category.

### How can I find overlapping date ranges?

The easiest way to check if a date range is overlapping another date range is shown in the following article: Identify overlapping date ranges

This article shows how to highlight overlapping date ranges based on a condition: Highlight records based on overlapping date ranges and a condition

This article explains how to calculate overlapping days between two date ranges: Days contained in a range that overlap another range

To count overlapping days across multiple date ranges, see these articles:

Count overlapping days

Count overlap. days(1)

Count overlap. days(2)

Check out the overlapping category for more articles.

### How can I identify overlapping time ranges?

Use MEDIAN function to calculate overlapping ranges

### How can I automatically create date ranges?

Follow the instructions on this web page: Create a date range [Formula]

### How do I combine or merge two columns?

### How do I combine or merge three columns?

Merge three columns into one list

### How do I combine or merge two tables?

Merge tables based on a condition

### How do I count a pattern in a cell value?

Count a given pattern in a cell value

### How do I count unique distinct values?

Count unique distinct values based on a condition

Count unique distinct values that meet multiple criteria

### How do I count unique values?

A unique value is a value that exists only once in a list, for example, value Pear exists only once in the list above so that value is counted as a unique value.

There are three unique values in the list above: Pear, Banana and Lemon. All other values has at least one duplicate value.

Formula in cell E3:

#### Explaining formula in cell E3

**Step 1 - Count each value**

The COUNTIF function counts the number of cells that equals a given value, however, if we use multiple values the function returns an array of values containing the number of values for each given value.

COUNTIF($B$3:$B$10,$B$3:$B$10)

becomes

COUNTIF({"Apple"; "Pear"; "Apple"; "Orange"; "Apple"; "Banana"; "Orange"; "Lemon"},{"Apple"; "Pear"; "Apple"; "Orange"; "Apple"; "Banana"; "Orange"; "Lemon"})

and returns {3; 1; 3; 2; 3; 1; 2; 1}.

**Step 2 - Identify unique values**

We know that a value is a unique value if the corresponding number in the array is equal to 1. The equal sign allows us to compare each number in the array. It returns the boolean value TRUE or FALSE.

COUNTIF($B$3:$B$10, $B$3:$B$10)=1

becomes

{3; 1; 3; 2; 3; 1; 2; 1}=1

and returns {FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE}.

*Step 3 - Multiply with 1*

The SUMPRODUCT function can't add boolean values so we must convert the boolean values to their numerical equivalents 1 and 0 (zero).

The parentheses let you change the order of calculation, we want to multiply the array with 1 and nothing else.

(COUNTIF($B$3:$B$10,$B$3:$B$10)=1)*1

becomes

({FALSE; TRUE; FALSE; FALSE; FALSE; TRUE; FALSE; TRUE})*1

and returns {0;1;0;0;0;1;0;1}.

**Step 4 - Add values**

SUMPRODUCT((COUNTIF($B$3:$B$10,$B$3:$B$10)=1)*1)

becomes

SUMPRODUCT({0;1;0;0;0;1;0;1})

and returns 3.

### How do I count duplicate values?

### How do I count cells containing text from list?

### How do I sort values using a formula?

### How do I count repetead contiguous values

### How do I extract unique distinct values?

### How do I extract duplicate values?

### How do I extract unique distinct records?

### How do I extract duplicate records?

Comments Off on Advanced Excel Formulas

## How to comment

How to add a formula to your comment<code>Insert your formula here.</code>

Convert less than and larger than signsUse 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 OscarYou can contact me through this contact form