## Sort text cells alphabetically from two columns

Table of Contents

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

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

**Answer:**

**Array formula in cell D2:**

Recommended article:

Sort a range from A to Z [Array formula]

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

This array formula handles duplicates but not blank cells.

### How to create an array formula

- Select cell D2
- Copy/Paste above formula
- Press and hold Ctrl + Shift
- Press enter once
- Release all keys

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

### How to copy array formula

- Select cell D2
- Copy (Ctrl + c)
- Select cell range D3:D11
- 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

Extract unique distinct values from a multi-column cell range

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

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

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

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

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

Sort a range from A to Z [Array formula]

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

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

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

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

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

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

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

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

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

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

### 13 Responses to “Sort text cells alphabetically from two columns”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Hi oscar,

Is possible to make a dropdown list as below?

[IMG]https://i65.photobucket.com/albums/h232/alien3660/dropdown.gif[/IMG]

condition:

1,two column of data

2,remove blank cells

3,only show one time of duplicated data

thank & best rgds,

senen

Senen,

Open attached file:

drop-down-list-from-two-columns-and-no-blanks.xlsx

Hi Oscar,

Thanks for ur reply,can u make it in .xls format b'cos i can't open .xlsx file in my pc...

sry for the inconvenience.

Thank & best rgds,

senen

Senen,

Try this file:

how-to-extract-a-unique-distinct-list-from-two-columns-in-excel-2003.xls

From the code listed, how does sending the sum of multiple COUNTIFs as 2nd parameter to MATCH() supposed to accomplish anything? MATCH() expects an array, not a value, for its 2nd parameter.

Anonymous,

The COUNTIFS functions return arrays in this formula.

Did you provide formula to sort andmerged between 2 or 3 list?

Thanks Oscar, great website, much learn from you....

How about 3 Columns? Merged and Sort them?

Sami

Sami,

I recommend using a custom function:

Merge,sort and remove blanks from multiple cell ranges

Need help :(

I have 10 sheets and all are the same format for each user to work on. column A list the order# which left alot of blank row at the bottom as we need to add more data day by days, all order# are difference for all sheets. and I have a master sheet. I want to combine all the order# which short from the 10 sheets ignore blank. please, avoiding VBA code because I know very little about it.

Thanks and best regard,

Rath

Rath,

Sorry, I don´t know using formulas but my add-in might be helpful:

https://www.get-digital-help.com/2010/02/28/combine-data-from-multiple-sheets-in-excel/#mergeranges

I have a problem how to combine and sort 2 columns with blanks? Is that possible with formula? No UDF or VBA?

thanks in advance

Sami

I made a udf:

https://www.get-digital-help.com/2009/07/01/sort-text-cells-alphabetically-from-two-columns-using-excel-array-formula/#sortudf