## 'Array manipulation' category

Subcategories

• CHOOSECOLS function (1)
• CHOOSEROWS function (1)
• DROP function (6)
• EXPAND function (2)
• HSTACK function (5)
• TAKE function (1)
• TOCOL function (18)
• TOROW function (2)
• VSTACK function (13)
• WRAPCOLS function (1)
• WRAPROWS function (1)

• Workaround for the TEXTSPLIT function – LAMBDA function
The TEXTSPLIT function works only with single cells. If you try to use a cell range the TEXTSPLIT function returns […]
Extract a unique distinct list across multiple columns and rows sorted based on frequency
This article demonstrates a formula that creates a frequency distribution table from a multi-column cell range which is useful in […]
How to use the EXPAND function
What is the EXPAND function? The EXPAND function increases a cell range or array by a specified number of columns […]
How to use the CHOOSECOLS function
The CHOOSECOLS function returns given columns from a cell range or array. Dynamic array formula in cell B9: =CHOOSECOLS(B2:D5, 2, […]
How to use the CHOOSEROWS function
The CHOOSEROWS function returns given rows from a cell range or array. Dynamic array formula in cell B9: =CHOOSEROWS(B2:D5, 2, […]
How to use the DROP function
The DROP function removes a given number of rows or columns from a 2D cell range or array. Dynamic array […]
How to use the TAKE function
The TAKE function returns a given number of rows or columns from a 2D cell range or array. Dynamic array […]
How to use the WRAPCOLS function
The WRAPCOLS function rearranges values from a single row to a 2D cell range based on a given number of […]
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: […]
How to use the TOROW function
The TOROW function rearranges values from a 2D cell range to a single row. Dynamic array formula in cell B8: […]
How to use the TOCOL function
The TOCOL function lets you rearrange values in 2D cell ranges to a single column. Dynamic array formula in cell […]
How to use the HSTACK function
What is the HSTACK function? The HSTACK function lets you combine cell ranges or arrays, it joins data to the […]
How to use the VSTACK function
The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the […]
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 […]
SUMIF across multiple sheets
This post describes two ways to a search multiple ranges and calculate a total of the corresponding values across worksheets […]
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 words containing a given string in a cell range
This post describes two ways to extract all matching strings from cells in a given cell range if they contain […]
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 […]
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 common values between two ranges
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
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 two columns
The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]
Merge three columns 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 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 […]
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 […]
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? […]