## Sort values in parallel in excel, part 2

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

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

Array formula in D5: =SMALL(IF(($B$3:$B$12<=$E$3)*($B$3:$B$12>=$E$2), $B$3:$B$12, "A"), ROWS($A$1:A1)) How to create an array formula Copy array formula Select cell D5 […]

The image above shows a table with two columns in cell range B3:C16, it contains random text values in column […]

Sort column based on frequency

Question: How do I create a new unique distinct list from a column. I also want the list sorted from large […]

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

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

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

Denisa asks: I have a problem and i cant figure it out, even if i'm seraching for 2 days. 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 range from A to Z [Array formula]

Question: How do I sort a range alphabetically using excel array formula? Answer: Cell range $B$2:$E$5 contains text values in random […]

### 4 Responses to “Sort values in parallel in excel, part 2”

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

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