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