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

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.

sort-from-a-to-z-from-two-columns.xls
(Excel 97-2003 Workbook *.xlsx)

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.

### 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