Sort values by corresponding text

I showed you in an earlier post how to sort text by number using a formula, it was a question from Denisa. The […]

Sort names by value

Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. I […]

Count per row

In this article I will demonstrate two techniques for counting per row. The first example is simple and straightforward. The second example is a […]

Lookup and return multiple values sorted in a custom order

Pat asks: Hi Oscar, Thanks for creating such a helpful website and I've a question if I would like to […]

Sort cell values in corresponding columns

krish asks: I've a query in case of two columns of dates and two columns of data. Calling columns A […]

Sort a table with an array formula

The sorted table to the right is created with an array formula using the data in the table to the left. Array […]

Find max unique value from a range that have duplicate numbers and blanks

Marc asks: How to find Min and Max numeric values in a range of cells that have duplicate numbers and […]

Sort values in a cell using a custom delimiter [VBA]

The following macro lets you select a cell range and a delimiting character. The macro sorts the values in each […]

Match a criterion and extract multiple corresponding table headers

Vikas asks: i need to extract the headers from a grid based on value in left most column example row […]

Sort values in an Excel table [VBA]

awall asks: Hey can you do the opposite of this - not random order but this is my situation. i […]

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]

List five smallest numbers, excluding zeros

Muhammad Saleem asks: I a column with over 400 entries. Most of them are 0s. I would like to list […]

How to sort a table in a custom order in excel [No formula]

Your boss wants you to sort the company´s products by a new criterion, quality. You receive a list from your […]

List people with the highest scores based on criteria in excel

Andre asks: I am tryng to list the people with the highest scores based on certain criteria. My data: column […]

Sort a list in random order in excel

Overview The array formula in cell range C2:C6 creates a random list from the values in cell range A2:A6. Array […]

Sort values in parallel in excel, part 2

Ralee asks in in this blog post: Sort values in parallel (array formula) If there is information in adjacent columns, […]

How to filter numbers inside (and outside) number ranges in excel

How to filter numbers inside ranges in column E and F Array formula in A2: =SMALL(IF(FREQUENCY(IF((COLUMN($A1:$U1)<=End)*(COLUMN($A1:$U1)>=Start), COLUMN($A1:$U1), ""), COLUMN($A1:$U1))>0, ROW($1:$21), […]

Sort numeric values by closest to farthest to a specific number in excel (array formula)

Example 1 This example demonstrates how to sort values with sort on right click menu. Formula in C5: =ABS($C$2-B5) + […]

Extract negative values and adjacent cells in excel

Table of Contents Extract negative values and adjacent cells (array formula) Extract negative values and adjacent cells (Excel Filter) Array […]

Sort values in parallel (array formula)

Table of contents How to sort a table by Column 1 and then by Column 2  (array formula) How to […]

Extract and sort text cells from a range containing both numerical and text values

Array formula in B16: =INDEX(tbl, MIN(IF(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1))=IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(tbl)-MIN(ROW(tbl))+1)), MATCH(SMALL(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), ROW(1:1)), INDEX(IF(ISTEXT(tbl), COUNTIF(tbl, "<"&tbl)), MIN(IF(SMALL(IF(ISTEXT(tbl), […]

Filter unique values sorted from A to Z

Introduction A unique value is a value that only exist once in a list. A unique distinct list contains all cell […]

Extract numbers and text from a range sorted from A to Z

This blog post describes how to filter text values and then numbers from a range. Array formula in B15: =INDEX(tbl, […]

Sort text values by length using array formula in excel

Array formula in B2: =IF(ROWS(B2:$B$2)>ROWS(List), "", INDEX(List, MATCH(LARGE((LEN(List)&","&ROW(List))*1, ROWS(B1:$B$1)), (LEN(List)&","&ROW(List))*1, 0))) + CTRL + SHIFT + ENTER copied down as […]

Filter duplicates from two columns combined and sort from A to Z using array formula in excel

This blog post describes how to extract duplicates sorted from A to Z from two different columns combined. Array formula […]

Reverse a list ignoring blanks in excel

Reverse a list ignoring blanks Formula in C2: =INDEX(List, LARGE(IF(List<>"", ROW(List)-MIN(ROW(List))+1, ""), ROWS($C$1:C1))) + CTRL + SHIFT + ENTER copied […]

Sort a range based on value frequency

Learn how to sort cell values by frequency.

Sort a range from A to Z using array formula in excel

Question: How do I sort a range alphabetically using excel array formula? Answer: Array formula in B8: =INDEX($B$2:$E$5, MIN(IF(SMALL(COUNTIF($B$2:$E$5, "<"&$B$2:$E$5), […]

Extract a list of alphabetically sorted duplicates from a column

Question: How do I create a new list of alphabetically sorted duplicates using excel array formula? Answer: Excel array formula […]

Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]

Sort dates within a date range

Question: I have a list of unsorted dates. I want to extract records between two dates and I want the […]

Sort a column alphabetically

Table of Contents Sort a column using array formula Two columns sorting by the second column Sort alphanumeric values I […]

How to sort cells filtered by two dates

Question: I have a list containg dates and values. How do I sort values between two specific dates? Answer: Yellow […]