## Sort values in parallel in excel, part 2

*Article updated on January 24, 2012*

**Ralee asks in in this blog post: Sort values in parallel (array formula)**

If there is information in adjacent columns, say:

Joel, AA, 7

Mark, FF, 31

Nick, AA, 7

with the possibility of matching sort criteria (columns 2 & 3), how would I display the corresponding data after the sort (sorting only columns 2 & 3) without repeats; that is,

Joel, AA, 7

Nick, AA, 7

Mark, FF, 31

Instead of:

Joel, AA, 7

Joel, AA, 7

Mark, FF, 31

?

**Answer:**

**Array formula in F2:**

+ CTRL + SHIFT + ENTER

**Array formula in G2:**

+ CTRL + SHIFT + ENTER copied down as far as needed.

**Array formula in H2:**

+ CTRL + SHIFT + ENTER copied down as far as needed.

**Named ranges**

Descr (C2:C15)

Qty (D2:D15)

What is named ranges?

**Explaining array formula in cell F2**

**Step 1 - **

**Download excel sample file for this tutorial**

Array-formula-sorts-columns-in-parallel2.xls

(Excel 97-2003 Workbook *.xls)

**Functions in this article:**

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

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

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

**ROW(**reference**)** returns the rownumber of a reference

**MATCH(**lookup_value;lookup_array; [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

**COUNTIF(**range,criteria**)**

Counts the number of cells within a range that meet the given condition

Extract unique distinct values from a filtered table [udf and array formula]

Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection […]Sort text cells alphabetically from two columns using excel array formula

Table of Contents Sort text from two columns combined (array formula) Sort text from multiple cell ranges combined (user defined […]### 4 Responses to “Sort values in parallel in excel, part 2”

### Leave a Reply

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

<code>your formula</code>

Remember to convert less than and larger than signs to html character entities before you post your comment.

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

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

VBA code

[/vb]

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

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Great! That's exactly what I needed.

~Thanks

there is an error is your named ranges

Qty (B2:B15) should be Qty (d2:d15)

Descr (B2:B15) should be Descr (c2:c15)

Paul,

thanks!

Please tell me why you put "65536" in your array formula

thx