'Array manipulation' category
Subcategories
The TEXTSPLIT function works only with single cells. If you try to use a cell range the TEXTSPLIT function returns […]
This article demonstrates a formula that creates a frequency distribution table from a multi-column cell range which is useful in […]
What is the EXPAND function? The EXPAND function increases a cell range or array by a specified number of columns […]
The CHOOSECOLS function returns given columns from a cell range or array. Dynamic array formula in cell B9: =CHOOSECOLS(B2:D5, 2, […]
The CHOOSEROWS function returns given rows from a cell range or array. Dynamic array formula in cell B9: =CHOOSEROWS(B2:D5, 2, […]
The DROP function removes a given number of rows or columns from a 2D cell range or array. Dynamic array […]
The TAKE function returns a given number of rows or columns from a 2D cell range or array. Dynamic array […]
The WRAPCOLS function rearranges values from a single row to a 2D cell range based on a given number of […]
The WRAPROWS function rearranges values from a single row to a 2D cell range. Dynamic array formula in cell B8: […]
The TOROW function rearranges values from a 2D cell range to a single row. Dynamic array formula in cell B8: […]
The TOCOL function lets you rearrange values in 2D cell ranges to a single column. Dynamic array formula in cell […]
What is the HSTACK function? The HSTACK function lets you combine cell ranges or arrays, it joins data to the […]
The VSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell at the […]
This article demonstrates ways to extract names and corresponding populated date ranges from a schedule using Excel 365 and earlier […]
This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]
This article demonstrates a recursive LAMBDA function and a User Defined Function (UDF) that multiplies numbers in each row with […]
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]
This post describes two ways to a search multiple ranges and calculate a total of the corresponding values across worksheets […]
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 calculate the number of times each word appears in a given range of cells. […]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
This formula returns multiple values even if they are arranged differently or have minor misspellings compared to the lookup value.
This post describes two ways to extract all matching strings from cells in a given cell range if they contain […]
Andre asks:I am trying to list people with the highest scores based on certain criteria. My data: column A B […]
The image above demonstartes an array formula in cell B34 that extracts empty hours in a weekly calendar. I have created […]
This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]
This article demonstrates how to calculate dates in a given date range (cells B13 and B14) that don't overlap the […]
This article demonstrates two formulas, they both accomplish the same thing. The Excel 365 formula is much smaller and is […]
This article demonstrates how to return the latest date based on a condition using formulas or a Pivot Table. The […]
This article demonstrates formulas that extract sorted unique distinct values from a cell range containing also blanks. Unique distinct values […]
The image above shows an array formula in cell B12 that extracts values shared by cell range B2:D4 (One) and […]
This article describes two formulas that extract duplicates from a multi-column cell range, the first one is built for Excel […]
The picture above shows how to merge two columns into one list using a formula. Table of Contents Merge two […]
The above image demonstrates a formula that adds values in three different columns into one column. Table of Contents Merge […]
This webpage demonstrates formulas that merge three non-adjacent cell ranges. What's on this webpage Extract a list of duplicates from […]
Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
This article describes how to count unique distinct values. What are unique distinct values? They are all values but duplicates are […]
This article demonstrates ways to list unique distinct values in a cell range with multiple columns. The data is not […]
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]