## 'Array manipulation' category

Subcategories

• CHOOSECOLS function (2)
• CHOOSEROWS function (2)
• DROP function (8)
• EXPAND function (3)
• HSTACK function (11)
• TAKE function (2)
• TOCOL function (24)
• TOROW function (3)
• VSTACK function (20)
• WRAPCOLS function (2)
• WRAPROWS function (2)

• Date and Time Functions – N to Z
Table of Contents How to use the NETWORKDAYS function How to use the NETWORKDAYS.INTL function How to use the NOW […]
Array Manipulation Functions
Table of Contents How to use the CHOOSECOLS function How to use the CHOOSEROWS function How to use the DROP […]
How to use the WRAPROWS function
The WRAPROWS function rearranges values from a single row to a 2D cell range. Dynamic array formula in cell B8: […]
Get date ranges from a schedule
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
Rearrange values in a cell range to a single column
Multiply numbers in each row by entire cell range
This article demonstrates a recursive LAMBDA function and a User Defined Function (UDF) that multiplies numbers in each row with […]
Working with classic ciphers in Excel
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]
Compare two columns and extract differences
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
Group rows based on a condition
What's on this page Group rows based on a condition Merge matching rows 1. Group rows based on a condition […]
Vlookup a cell range and return multiple values
VLOOKUP a multi-column range and return multiple values.
VLOOKUP – Return multiple unique distinct values
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Filter unique distinct records case sensitive
This article demonstrates two ways to extract unique and unique distinct rows from a given cell range. The first one […]
Analyze word frequency in a cell range
This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]
Fuzzy lookups
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
Fuzzy VLOOKUP
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
Filter strings containing a given substring in a cell range
This post describes ways to extract all matching strings from cells in a given cell range if they contain a […]
Sort rows based on frequency and criteria
Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]
Find empty hours in a weekly schedule
The image above demonstartesÂ an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]
Convert date ranges into dates
List dates outside specified date ranges
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
Merge two columns with possible blank cells
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]
Find the most recent date that meets a particular condition
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
How to list unique distinct values sorted by frequency
What's on this page Unique distinct values sorted based on frequency (single column) Unique distinct values sorted based on frequency […]
Extract unique distinct values A to Z from a range and ignore blanks
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
Filter values in common between two cell ranges
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
Filter values occurring in range 1 but not in range 2
Table of Contents Filter values occurring in range 1 but not in range 2 Filter not shared values out of […]
Extract duplicates from a multi-column cell range
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
Merge cell ranges into one list
Extract a list of duplicates from three columns combined
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
Extract a list of duplicates from a column
The array formula in cell C2 extracts duplicate values from column A. Only one duplicate of each value is displayed […]
Extract a unique distinct list from three columns
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
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 […]
How to create date ranges in Excel
Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]
Find missing numbers
Table of Contents Find missing numbers in a column based on a given range Find missing three character alpha code […]
Count unique distinct values
This article describes how to count unique distinct values. What are unique distinct values?Â They are all values but duplicates are […]
Working with unique values
What's on this page Extract unique values from two columns - Excel 365 Extract unique values from two columns - […]
Extract unique distinct values from a multi-column cell range
This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]
How to extract email addresses from an Excel sheet
Question: How to extract email addresses from this sheet? Answer: It dependsÂ on how the emails are populated in your worksheet? […]
Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]