'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
    This article demonstrates formulas that rearrange values in a cell range to a single column. Table of Contents Rearrange cells […]
    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
    This article demonstrates how to create a list of dates based on multiple date ranges. Table of contents Convert date […]
    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
    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 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? […]

    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

    Excel categories