Question: How do I sort text values in two columns combined?

Answer:

sort-two-columns-alphabetically

Array formula in cell D2:

=IF(ISERROR(MATCH(ROWS(D$2:$D2)-1, COUNTIF(List1, "<"&List1)+COUNTIF(List2, "<"&List1)+IF((COUNTIF(List1, List1)+COUNTIF(List2, List1))<=COUNTIF($D$1:D1, List1), 0, COUNTIF($D$1:D1, List1)), 0)), INDEX(List2, MATCH(ROWS(D$2:$D2)-1, COUNTIF(List2, "<"&List2)+COUNTIF(List1, "<"&List2)+IF((COUNTIF(List2, List2)+COUNTIF(List1, List2))<=COUNTIF($D$1:D1, List2), 0, COUNTIF($D$1:D1, List2)), 0)), INDEX(List1, MATCH(ROWS(D$2:$D2)-1, COUNTIF(List1, "<"&List1)+COUNTIF(List2, "<"&List1)+IF((COUNTIF(List1, List1)+COUNTIF(List2, List1))<=COUNTIF($D$1:D1, List1), 0, COUNTIF($D$1:D1, List1)), 0)))

This array formula handles duplicates but not blank cells.

How to create an array formula

  1. Select cell D2
  2. Copy/Paste above formula
  3. Press and hold Ctrl + Shift
  4. Press enter once
  5. Release all keys
How to copy array formula
  1. Select cell D2
  2. Copy (Ctrl + c)
  3. Select cell range D3:D11
  4. Paste (Ctrl + v)

Named ranges
List1 (A2:A5)
List2 (B2:B6)
What is named ranges?

How to implement array formula to your workbook
Change named ranges. If your list starts at, for example, F3. Change D1:$D$1 in the above formula to F2:$F$2.

Download excel sample file for this tutorial
sort-from-a-to-z-from-two-columns.xls
(Excel 97-2003 Workbook *.xlsx)

Functions in this article:

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

ROWS(array) returns the number of rows in a reference or an array

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 the given condition

ISERROR(value)
Checks whether a value is an error and returns TRUE or FALSE

This blog article is one out of five articles on the same subject.

Sorting text cells using array formula in excel
Sorting numbers and text cells also removing blanks using array formula in excel
Sorting numbers and text cells descending also removing blanks using array formula in excel
Sort text cells alphabetically from two columns using excel array formula
Create a drop down list containing only unique distinct alphabetically sorted text values using excel array formula
Sort a range from A to Z using array formula in excel

Related posts:

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

Merge two columns with possible blank cells in excel (formula)

Comparing two columns and sum unique values using array formula in excel

Extract a list of duplicates from two columns combined using array formula in excel

Extract a list of duplicates from three columns combined using array formula in excel