## Extract a unique distinct list by matching items that meet a criterion in excel

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

**How to enter an aray formula**

- Copy (Ctrl + c) above formula
- Double click on cell E8
- Paste (Ctrl + v) to cell
- Press and hold CTRL + Shift simultaneously
- Press Enter
- 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.

**Formula in G8:**

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

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

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

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

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

### Download excel *.xls file

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

### 39 Responses to “Extract a unique distinct list by matching items that meet a criterion in excel”

### Leave a Reply

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

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

your 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

This really is brilliant. Can I ask for one modification? How can I count the number of transactions per person?

Anura,

Formula in H8:

=SUMPRODUCT(--(E8=$A$2:$A$11)) + ENTER.

That is just awesome.

I tried equating the value "Branch A" itself in the formula instead of its address and it worked perfectly.

Thanks Oscar!

Vashi,

You are welcome!

Occar, your breakdown above worked for me, HOWEVER, i am using a date vs. Branch A. Needless to say, my spreadsheet is dynamic so i need the $F$1, in this scenario to map to a different page. When i simply change the formula to ='High Level Summary'!U3 (result = 1/10/2013 for example), my results turn to errors. It works fine as long as my date is just a date and not a formula. I even tried using INDIRECT prior to ='High Level Summary'!U3. A dropdown box is what dictates the date to be used. PLEASE HELP.... :(

Jana

Jana, can you provide the formula? Maybe your dates are not excel dates?

How excel stores dates

Oscar,

This is a pretty awesome solution!

I am trying to include your formula in an dashboard that shows dynamic results depending on the look-up value. I'm struggling with hiding error values (i.e. "#N/A") for the formula in the "Name" column.

I've already tried a few methods for hiding zero and error values that work with other formulas, but I can't seem to make anything work for your formula.

Can you help?

Thanks!

Dave

Looks like one way to do it is:

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

This is a thing of beauty.

Very helpful - thank you!

dubdub,

thank you for commenting!

Thank you. This was very helpful as I wanted to extract personal data for league members (using the team as the criteria) from a league data sheet to a team data sheet.

I used David Myers' suggestion for ignoring #N/A error messages in empty rows but wonder if anyone can suggest a way to "hide" the zeros that appear when I have an incomplete row with empty cells. I assume I will need to use a nested if function in the column reference but can't get the syntax right.

I would really value any suggestions.

Thanks

Does this work if your data is on a different tab?

Tim,

Yes, change cell references.

Example,

=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(sheet2!$A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, sheet2!$A$2:$A$11)+(sheet2!$B$2:$B$11<>$F$1), 0), COLUMN(A1))

Hi ,

Is these work on the more column. i have 1400 column and this formula not working.

data is much bigger.

I am want agent project list.

i want list of project on which agent has worked.

Regards,

Krunal

Krunal,

Yes, you need a custom function (vba).

how would you add another criteria, I am looking to do this with the date and time as the criteria to match.

john dalton,

If the start date is in F1 and end date is in F2 and the dates are in cell range B2:B11:

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

How do I add another additional criteria? Say if I wanted to pull a unique list of branches and invidiuals

Justine,

perhaps this is helpful:

Filter unique distinct row records in excel 2007

Hi Oscar,

I need help as well. Suppose that i needed to find the top 20 names with the highest amounts in a Branch. I've tried with the LARGE function but i can't make it work.

Thanks

Hello,

Thanks for this! It's saved me. However, I would like to modify this formula so that it doesn't summarize by name. So using your example, I would want to see Frank's name three times, Joe's name three times, etc. I want my unique distinct list to be the same as the original list, I just want to filter out those names that aren't Branch A.

Thanks.

Chuck

Array formula in cell E8:

=INDEX($A$2:$C$11, SMALL(IF($F$1<>$B$2:$B$11, MATCH(ROW($B$2:$B$11), ROW($B$2:$B$11)), ""), ROW(A1)), COLUMNS($A$1:A1))

Thanks, Oscar! Works like a champ. However, I would like to point out that this formula only works if your original table starts in A1. So the last part of your formula:

...ROW(A1)), COLUMNS($A$1:A1))

I had to come up with another way to get the correct values here. Once I did it worked great. Thank you.

Hi Oscar,

This works as a charm, however (as in previous comment) I need value be displayed all times it appears in the list. Is there any way to tell this formula not to ignore repetitive values?

I mean the result should look like

Frank Branch A

Frank Branch A

Frank Branch A

Joe Branch A

Mike Branch A

Joe Branch A

Joe Branch A

Thanks in advance!

I don't have any questions. I just want to say that you are awesome.

Thanks for your helpful and clear examples. Following your same example – how about if from a long list of branches, I need to extract the amounts of sub-set of branches. For examples, need the amounts from 7 out of 20 branches, just to illustrate an example. How do I name the string of non-consecutive branch numbers? Thanks again

Thanks for your helpful and clear examples. Following your same example – how about if from a long list of branches, I need to extract the amounts of sub-set of branches. For examples, need the amounts from 7 out of 20 branches, just to illustrate an example. How do I specify a string of non-consecutive branch numbers?

Sorry for the multiple entries, something is wrong on this end

Hi Oscar,

I dint understand how this countif is working. I can see that the range is smaller than the criteria. Can you please explain the logic through the first principles.

Thanks.

Hi Chuck

How can I sort my data while fetching. Lets say, I want my data to be sorted ascending order wise depending on amount. I don't want a help column. How can I do it in one go, while I am pulling the data using your formula

I have what I think is a very simple process I want to do, but I cannot find the answer on the internet because I do not know what to call it in 'excel speak'. I have a list of students and their reading level (numerical). I just want it to dynamically create lists of all the students in each level.

e.g.

Students Level

studentA 2

studentB 9

studentC 7

studentD 3

studentE 9

etc (in two columns) and I want to then create a table with the levels as the headings and the students names to automatically go under the level.

e.g.

1 2 3... ...9

studentA studentB

student E

How can I do this?

Hi Oscar,

I'm starting to learn Excel now. And I have a spreadsheet similar to a data entry. My job is to go through weekly data to see which kid is in trouble. My question is, Is there a formula I can set where it list down data from a certain range. For example if I want to view data from the 5/18/2016 to 5/24/2016.

Thanks

John

I recommend you convert your data table to an excel defined table and apply a date filter to your date column.

https://support.office.com/en-us/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

Hi there,

I have a dataset and I wish to show, in a separate tab, a distinct list based on two criterion. What would I need to do?

Thanks, Kevin

Kevin,

This formula has one criterion (bolded):

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+

($B$2:$B$11<>$F$1), 0), COLUMN(A1))If you need two criteria, see this formula:

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+

($B$2:$B$11<>$F$1)*($A$2:$A$11<>"Frank"), 0), COLUMN(A1))It looks for a criterion in column A and another criterion in column B.

This formula looks for two criteria ($F$1:$F$2) in cell range $B$2:$B$11:

=INDEX($A$2:$C$11, MATCH(0, COUNTIF($E$7:$E7, $A$2:$A$11)+

COUNTIF($F$1:$F$2,"<>"&$B$2:$B$11), 0), COLUMN(A1))Hi Oscar,

I'm using a very similar formula to yours:

=IFERROR(INDEX(all_names, MATCH(0, IF($D$2=all_groups, COUNTIF(D$4:D4, all_names), ""), 0)),"")

This works well. However, if I try to change it to include an OR function it no longer work:

=IFERROR(INDEX(all_names, MATCH(0, IF(OR($D$2=all_groups,$D$2=all_groups_2,$D$2=all_groups_3), COUNTIF(D$4:D4, all_names), ""), 0)),"")

Any idea what I'm doing wrong?

Thanks,

Jake

Jake,

Try this:

=IFERROR(INDEX(all_names, MATCH(0, IF(($D$2=all_groups)+($D$2=all_groups_2)+($D$2=all_groups_3), COUNTIF(D$4:D4, all_names), ""), 0)),"")

Hello Oscar,

What code is needed to cause cells in Columns F - I to fill with the contents of Columns C - E when a cell in Column B includes a numeric value?

Liam,

See this post:

http://www.get-digital-help.com/2016/12/20/filter-rows-where-a-cell-contains-a-numeric-value/