Table of Contents

  1. Sort text from two columns combined (array formula)
  2. Sort text from multiple cell ranges combined (user defined function)


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

Recommended article:

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), […]

Comments(3) Filed in category: Excel, Sort values

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

Learn the basics of Excel arrays

Array formulas allows you to do advanced calculations not possible with regular formulas.

Comments(2) Filed in category: Count values, Excel

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.

Recommended article

Unique distinct values from multiple columns using array formula

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Comments(23) Filed in category: Excel, Unique distinct values

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

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:

Sort a column alphabetically

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

Comments(81) Filed in category: Excel, Sort values

Sorting numbers and text cells also removing blanks using an array formula

Question: How do I sort text and numbers and also removing blanks using an array formula? Answer: Array formula in […]

Comments(22) Filed in category: Excel

Create a drop down list containing only unique distinct alphabetically sorted text values

Question: How do I create a drop-down list with unique distinct alphabetically sorted values? Table of contents Sort values using […]

Comments(76) Filed in category: Drop down lists, Excel

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), […]

Comments(3) Filed in category: Excel, Sort values


Sort text from multiple cell ranges combined (user defined function)

sort-text-from-multiple-cell-ranges-combined-user-defined-function
This user defined function allows you to enter up to 255 arguments or cell ranges. The udf combines all values from all cell ranges and then sorts them from A to Z. It uses a bubblesort algorithm and I don't recommend using large data sets.

Array formula in cell F2:F12:

=SortMultipleRanges(A2:B3,C4:C6,D8:D9)

VBA Code

Function SortMultipleRanges(ParamArray rng() As Variant)
Dim temp() As Variant

'Count cells
For Each cellrange In rng
i = i + cellrange.Cells.CountLarge
Next cellrange
ReDim temp(1 To i, 1 To 1)

i = 0
For Each cellrange In rng
For Each cell In cellrange
i = i + 1
If cell &lt;&gt; "" Then temp(i, 1) = cell
Next cell
Next cellrange

SortMultipleRanges = BubbleSort(temp)

End Function
Function BubbleSort(str As Variant)
Dim tmp As String, c As Integer, temp As String
Dim a As Long, b As Long

For a = LBound(str, 1) To UBound(str, 1)

For b = a + 1 To UBound(str, 1)

If str(a, 1) &gt; str(b, 1) Then

tmp = str(a, 1)

str(a, 1) = str(b, 1)

str(b, 1) = tmp

End If

Next b

Next a

BubbleSort = str

End Function

Download excel *.xlsm file

bubblesort-2-or-more-ranges.xlsm