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:

=INDEX($B$2:$B$4, MATCH(SMALL(COUNTIF(Descr, "<"&Descr)+(1/(COUNTIF(Qty, ">"&Qty)+1+ROW(Qty)/65536)), ROW(1:1)), COUNTIF(Descr, "<"&Descr)+(1/(COUNTIF(Qty, ">"&Qty)+1+ROW(Qty)/65536)), 0))

+ CTRL + SHIFT + ENTER

Array formula in G2:

=INDEX(Descr, MATCH(SMALL(COUNTIF(Descr, "<"&Descr), ROW(1:1)), COUNTIF(Descr, "<"&Descr), 0))

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

Array formula in H2:

=INDEX(Qty, MATCH(SMALL(COUNTIF(Descr, "<"&Descr)+(1/(COUNTIF(Qty, ">"&Qty)+1)), ROW(1:1)), COUNTIF(Descr, "<"&Descr)+(1/(COUNTIF(Qty, ">"&Qty)+1)), 0))

+ 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