'Array manipulation' category
Subcategories
The TEXTSPLIT function works only with single cells. If you try to use a cell range the TEXTSPLIT function returns […]
Cell range B2:E11 contains values, the formula in cell B15 extracts unique distinct values in B2:E11, ignores blanks, and returns […]
The EXPAND function increases a cell range or array by a specified number of columns and rows. Dynamic array formula […]
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 […]
The HSTACK function lets you combine cell ranges or arrays, it joins data to the first blank cell 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 […]
What's on this page Reverse text Insert random characters Convert letters to numbers How to shuffle characters in the alphabet […]
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 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 […]
Question: How to extract email addresses from this sheet? Answer: It depends on how the emails are populated in your worksheet? […]