Anura asks:

Is it possible to extend this by matching items that meet a criteria?

I have a list of credit card transactions showing the name of the card holder, their Branch and the amount. I want to produce a report for each Branch, so what I want is to extract only those people who match the Branch name. For example:

Frank Branch A
Frank Branch A
Frank Branch A
Joe Branch A
Mary Branch B
Jane Branch C
Mike Branch A
Joe Branch A
Dave Branch C

I would like a list of only those people for Branch A, and then be able to summarise the transactions. Or should I do this in two stages?

Answer:

Type a branch in cell F1.

Array formula in E8:

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1), 0), COLUMN(A1)) + CTRL + SHIFT + ENTER. Copy cell E8 and paste it down as far as needed. Copy cells and paste into cell range F8 and down as far as needed.

Formula in G8:

=SUMPRODUCT($C$2:$C$11, --($B$2:$B$11=$F$1), --(E8=$A$2:$A$11)) + Enter. Copy cell G8 and paste it down as far as needed.

Next blog post is about how to accomplish this by creating a pivot table.

Download excel sample file for this tutorial.

unique distinct list matching criteria.xls
(Excel 97-2003 Workbook *.xls)

Previous articles:

Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel
Filter unique distinct values where adjacent cells contain search string in excel
Filter unique distinct list sorted based on sum of adjacent values using array formula in excel
Excel – Unique and duplicate values

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

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

ROW(reference) returns the rownumber of a reference

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

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

SUMPRODUCT(array1, array2, )
Returns the sum of the products of the corresponding ranges or arrays