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:

This post demonstrates how to build a formula to extract a unique distinct list using a condition, however I highly recommend a pivot table for this task: Pivot table - Unique distinct list

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

Recommended link

How to extract a unique distinct list from a column in excel

You have quite a few options to choose from if you are looking for a way to create a unique […]

Comments(149) Filed in category: Advanced filter, Excel, Unique distinct values

How to enter an aray formula

  1. Copy (Ctrl + c) above formula
  2. Double click on cell E8
  3. Paste (Ctrl + v) to cell
  4. Press and hold CTRL + Shift simultaneously
  5. Press Enter
  6. Release all keys

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.

Recommended link

Learn the basics of Excel arrays

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

Comments(2) Filed in category: Built-in features, Count values, Excel

Formula in G8:

=SUMPRODUCT($C$2:$C$11, --($B$2:$B$11=$F$1), --(E8=$A$2:$A$11))

Copy cell G8 and paste it down as far as needed. If you are interested in how the SUMPRODUCT function works, read:

How to use excel SUMPRODUCT function

Multiplies cell ranges and then sum all values.

Comments(2) Filed in category: Excel, Functions

Explaining array formula in cell E8

Step 1 - Check previous values to make sure they are not repeated

COUNTIF($E$7:$E7, $A$2:$A$11)

If you examine the cell references you may find this strange: $E$7:$E7 It is an absolute and relative cell reference. It expands when the formula is copied across the spreadsheet. Read more here: Absolute and relative references in excel

COUNTIF($E$7:$E7, $A$2:$A$11)

becomes

COUNTIF("Name", {"Frank"; "Frank"; "Frank"; "Joe"; "Mary"; "Jane"; "Mike"; "Joe"; "Dave"; "Joe"})

and returns

{0;0;0;0;0;0;0;0;0;0}

Recommended article

COUNTIF function

Counts the number of times a value exists in a cell range.

Comments(5) Filed in category: Excel, Functions

Step 2 - Compare values in column B with the value in cell F1

($B$2:$B$11<>$F$1)

becomes

{"Branch A"; "Branch A"; "Branch A"; "Branch A"; "Branch B"; "Branch C"; "Branch A"; "Branch A"; "Branch C"; "Branch A"}<>"Branch A"

and returns {FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE}

Step 3 - Add arrays

COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1)

becomes

{0;0;0;0;0;0;0;0;0;0} + {FALSE; FALSE; FALSE; FALSE; TRUE; TRUE; FALSE; FALSE; TRUE; FALSE}

and returns

{0;0;0;0;1;1;0;0;1;0}

Step 4 - Find first 0 (zero) in array

MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1), 0)

becomes

MATCH(0, {0;0;0;0;1;1;0;0;1;0}, 0)

and returns 1.

Recommended article

MATCH function

Identify the position of a value in an array.

Comments(12) Filed in category: Excel, Functions

Step 5 - Return a value or reference of the cell at the intersection of a particular row and column

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+($B$2:$B$11<>$F$1), 0), COLUMN(A1))

becomes

=INDEX($A$2:$C$11, 1, COLUMN(A1))

becomes

=INDEX($A$2:$C$11, 1, 1)

becomes

=INDEX({"Frank","Branch A",10; "Frank","Branch A",20; "Frank","Branch A",40; "Joe","Branch A",30; "Mary","Branch B",5; "Jane","Branch C",10; "Mike","Branch A",15; "Joe","Branch A",40; "Dave","Branch C",35; "Joe","Branch A",20}, 1, 1)

and returns "Frank" in cell E8.

Recommended link

INDEX function explained

Fetch a value in a data set based on coordinates.

Comments(14) Filed in category: Excel, Functions, Index

Download excel *.xls file

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

Recommended articles:

Filter unique distinct records with a condition

Sean asks: If Tea and Coffee has Americano,it will only return Americano once and not twice. I am looking for a […]

Comments(3) Filed in category: Excel, Unique distinct records

Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel

Question: How do I create a distinct list from column C (Values) where cell values in column B (Category) equals […]

Comments(15) Filed in category: Excel, Unique distinct values

Filter unique distinct values where adjacent cells contain search string in excel

Question: How do I create a unique distinct list where adjacent cell values contains a search string? AA102 CA AA103 […]

Comments(16) Filed in category: Excel, Unique distinct values

Filter unique distinct list sorted based on sum of adjacent values using array formula in excel

Table of Contents Filtering unique distinct text values and sort them based on sum of adjacent values Filtering unique distinct […]

Comments(10) Filed in category: Excel, Sorted unique distinct 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 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