In a previous article How to sort cells filtered by two dates I came up with a solution on how to sort cells containing numbers, filtered by two dates. In this article I will sort cells containing text values.

Chandoo at Pointy haired Dilbert posted a great article Sorting Text in Excel using Formulas that has inspired me.
I will use Excel´s countif function in this article.

COUNTIF(range,criteria)
Counts the number of cells within a range that meet a given condition

Answer: Yellow cells are input cells. The gray area is the sorted value list.

sorted-list-4


Here is the formula in G4:G17

=IF(SUM(IF(($A$4:$A$17<E4)*($A$4:$A$17>D4),1,0))>=ROW()-3,INDEX(C4:C17, MATCH(SMALL(IF(($A$4:$A$17<E4)*($A$4:$A$17>D4), COUNTIF($C$4:$C$17,"<"&$C$4:$C$17)+1,""),ROW()-3), COUNTIF($C$4:$C$17,"<"&$C$4:$C$17)+1,0)),"") + Ctrl + Shift + Enter

Download excel sample file for this tutorial.
sort-text-cells-between-two-dates
(Excel 97-2003 Workbook *.xls)

Functions in this article:

MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

IF(logical_test;[value_if:true];[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

COUNTIF(range,criteria)
Counts the number of cells within a range that meet a given condition

SMALL(array,k) returns the k-th smallest row number in this data set.

ROW(reference) returns the rownumber of a reference

SUM(number1,[number2],)
Adds all the numbers in a range of cells


Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. How to sort cells filtered by two dates
  2. Extract and sort text cells from a range containing both numerical and text values
  3. Sort text cells alphabetically from two columns using excel array formula
  4. Sort dates within a date range using excel array formula
  5. Sorting numbers and text cells also removing blanks using array formula in excel
  6. Most common value between two dates in a range in excel
  7. Sorting text cells using array formula in excel
  8. Sorting numbers and text cells descending also removing blanks using array formula in excel
  9. Sort text values by length using array formula in excel
  10. Most common value between two dates in excel