'Array manipulation' category
Subcategories
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 […]
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
What is the CHOOSECOLS function? The CHOOSECOLS function returns given columns from a cell range or array. The image above […]
What is the CHOOSECOLS function? The CHOOSECOLS function returns given columns from a cell range or array. The image above […]
How to use the CHOOSEROWS function
What is the CHOOSEROWS function? The CHOOSEROWS function returns given rows from a cell range or array. The CHOOSEROWS function […]
What is the CHOOSEROWS function? The CHOOSEROWS function returns given rows from a cell range or array. The CHOOSEROWS function […]
How to use the DROP function
What is the DROP function? The DROP function removes a given number of rows or columns from a 2D cell […]
What is the DROP function? The DROP function removes a given number of rows or columns from a 2D cell […]
How to use the TAKE function
What is the TAKE function? The TAKE function returns a given number of rows or columns from a 2D cell […]
What is the TAKE function? The TAKE function returns a given number of rows or columns from a 2D cell […]
How to use the WRAPCOLS function
What is the WRAPCOLS function? The WRAPCOLS function rearranges values from a single row to a 2D cell range based […]
What is the WRAPCOLS function? The WRAPCOLS function rearranges values from a single row to a 2D cell range based […]
How to use the WRAPROWS function
What is the WRAPROWS function? The WRAPROWS function rearranges values from a single row to a 2D cell range. Table […]
What is the WRAPROWS function? The WRAPROWS function rearranges values from a single row to a 2D cell range. Table […]
How to use the TOROW function
What is the TOROW function? The TOROW function rearranges values from a 2D cell range to a single row. Dynamic […]
What is the TOROW function? The TOROW function rearranges values from a 2D cell range to a single row. Dynamic […]
How to use the TOCOL function
What is the TOCOL function? The TOCOL function lets you rearrange values in 2D cell ranges to a single column. […]
What is the TOCOL function? The TOCOL function lets you rearrange values in 2D cell ranges to a single column. […]
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 […]
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 […]
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 […]
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
Rearrange values using formulas
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]
The picture above shows data presented in only one column (column B), this happens sometimes when you get an undesired […]
Time sheet for work
Tracking work hours across multiple projects can be challenging but Excel makes it easy with built-in functions. In this guide, […]
Tracking work hours across multiple projects can be challenging but Excel makes it easy with built-in functions. In this guide, […]
Compare two columns in different worksheets
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
This article describes an array formula that compares values from two different columns in two worksheets twice and returns a […]
Find and return the highest number and corresponding date based on a condition
Table of Contents Introduction Find and return the highest number and corresponding date based on a condition - Excel 365 […]
Table of Contents Introduction Find and return the highest number and corresponding date based on a condition - Excel 365 […]
Dynamic scoreboard
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
This article demonstrates a scoreboard, displayed to the left, that sorts contestants based on total scores and refreshes instantly each […]
Working with overlapping date ranges
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
This article demonstrates formulas that calculate the number of overlapping ranges for all ranges, finds the most overlapped range and […]
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 […]
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 […]
This article demonstrates formulas that extract values that exist only in one column out of two columns. There are text […]
Excel calendar
Table of Contents Excel monthly calendar - VBA Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]
Table of Contents Excel monthly calendar - VBA Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]
How to compare two data sets
Table of Contents How to compare two data sets - Excel Table and autofilter Filter shared records from two tables […]
Table of Contents How to compare two data sets - Excel Table and autofilter Filter shared records from two tables […]
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. […]
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 […]
This article demonstrates two ways to extract unique and unique distinct rows from a given cell range. The first one […]
Fuzzy VLOOKUP
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
Compare tables: Filter records occurring only in one table
Table of Contents Compare tables: Filter records occurring only in one table Compare two lists and filter unique values where […]
Table of Contents Compare tables: Filter records occurring only in one table Compare two lists and filter unique values where […]
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 […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
5 easy ways to VLOOKUP and return multiple values
This post explains how to lookup a value and return multiple values. No array formula required.
This post explains how to lookup a value and return multiple values. No array formula required.
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 […]
What's on this page Unique distinct values sorted based on frequency (single column) Unique distinct values sorted based on frequency […]
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 […]
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
Search for a text string in a data set and return multiple records
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]
Merge cell ranges into one list
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]
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 […]
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 multiple non adjacent cell ranges
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
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 […]
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 […]
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 […]
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 - […]
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 […]
This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]
Calculate machine utilization
This article explains how to calculate an overlapping time ranges across multiple days. This can be very useful in situations […]
This article explains how to calculate an overlapping time ranges across multiple days. This can be very useful in situations […]
Filter unique distinct records
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
Table of contents Filter unique distinct row records Filter unique distinct row records but not blanks Filter unique distinct row […]
Excel formula categories
AverageChooseCombine MergeCompareConcatenateConditional FormattingCountCount valuesDatesDuplicatesExtractFilterFilter recordsFiltered valuesFilterxmlHyperlinkIf cellIndex MatchLogicLookupsMatchMaxMinNumbers in sumOR logicOverlappingPartial matchRecordsSequenceSmallSort bySort valuesSumSumifsSumproductString manipulationTimeUnique distinct recordsUnique distinct valuesUnique recordsUnique valuesVlookupVlookup return values