## How to create a unique distinct list based on two conditions

**Question:** How do I create a unique distinct list where other columns meet two criteria using excel array formula?

**Answer:**

### Excel 2007 array formula in H2:

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

Copy cell H2. Paste down to cell H16.

### Earlier Excel versions, array formula in H2:

Copy cell H2. Paste down to cell H16.

### Named ranges

List (C2:C16)

Category1 (A2:A16)

Category2 (B2:B16)

Criteria1 (F1)

Criteria2 (F2)

### How to create named ranges

- Select cell range C2:C16
- Type
*List*in name box

### Explaining excel 2007 array formula in cell H2

**Step 1 - Create boolean array with values indicating unique distinct values**

=IFERROR(INDEX(List, MATCH(0, **COUNTIF(H1:$H$1, List)**+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")

COUNTIF(H1:$H$1, List)

becomes

COUNTIF("Distinct unique list", {4; 2; 1; 2; 2; 4; 2; 3; 1; 1; 3; 4; 2; 5; 7})

and returns {0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}.

How to use the COUNTIF function

Counts the number of cells that meet a specific condition.

How to use the COUNTIF function

**Step 2 - Create boolean array with values indicating first criterion**

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+**IF(Category1<>Criteria1, 1, 0)**, 0)), "")

IF(Category1<>Criteria1, 1, 0)

becomes

IF({"BB"; "AA"; "AA"; "BB"; "BB"; "BB"; "BB"; "BB"; "AA"; "BB"; "BB"; "BB"; "AA"; "BB"; "BB"}<>"BB", 1, 0)

and returns {0;1;1;0;0;0;0;0;1;0;0;0;1;0;0}.

Checks if a logical expression is met. Returns a specific value if TRUE and another specific value if FALSE.

**Step 3 - Create boolean array with values indicating second criterion**

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+**IF(Category2<>Criteria2, 1, 0)**+IF(Category1<>Criteria1, 1, 0), 0)), "")

IF(Category2<>Criteria2, 1, 0)

becomes

IF({"A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "A"; "B"; "C"}<>"A", 1, 0)

and returns {0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 1}.

**Step 4 - Add boolean arrays**

=IFERROR(INDEX(List, MATCH(0, COUNTIF(H1:$H$1, List)+IF(Category2<>Criteria2, 1, 0)+IF(Category1<>Criteria1, 1, 0), 0)), "")

becomes

=IFERROR(INDEX(List, MATCH(0,{0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0}+{0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 0; 1; 1}+{0;1;1;0;0;0;0;0;1;0;0;0;1;0;0}, 0)), "")

becomes

=IFERROR(INDEX(List, MATCH(0,{0;2;1;1;0;1;0;1;1;1;0;1;1;1;1}, 0)), "")

**Step 4 - Return relative position of the first zero (0) in array**

=IFERROR(INDEX(List, MATCH(0,{0;2;1;1;0;1;0;1;1;1;0;1;1;1;1}, 0)), "")

becomes

=IFERROR(INDEX(List, 1), "")

Identify the position of a value in an array.

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

=IFERROR(INDEX(List, 1), "")

becomes

=IFERROR(4, "")

Gets a value in a specific cell range based on a row and column number.

**Step 6 - Check if expression returns an error**

=IFERROR(4, "")

returns number 4 in cell H2.

How to use the IFERROR function

The IFERROR function lets you catch most errors in Excel formulas. It was introduced in Excel 2007. In previous Excel […]

How to use the IFERROR function

### Get excel file.

create-a-list-of-distinct-values-from-two-criteria.xlsx

(Excel 2007 Workbook *.xlsx)

create-a-list-of-distinct-values-from-two-criteria.xls

(Excel 97-2003 Workbook *.xls)

5 easy ways to extract Unique Distinct Values

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

Extract a unique distinct list from two columns

Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]

Vlookup – Return multiple unique distinct values

Ahmed Ali asks: How to return multiple values using vlookup in excel and removing duplicates? I have tried the formula […]

Extract a unique distinct list sorted from A to Z

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. […]

Extract a unique distinct list and sum amounts based on a condition

Anura asks: Is it possible to extend this by matching items that meet a criteria? I have a list of […]

Extract a unique distinct list from three columns

Question: How do I extract a unique distinct list from three ranges or lists? The ranges are not necessarily adjacent […]

Extract a unique distinct list and ignore blanks

This article demonstrates formulas that extract unique distinct values and ignore blank empty cells. Table of contents Extract a unique […]

Extract unique distinct values from a multi-column cell range

Question: I have cell values spanning over several columns and I want to create a unique list from that range. […]

Extract unique distinct values A to Z from a range and ignore blanks

This is an answer to a question in this blog post: Extract a unique distinct list sorted from A-Z from […]

### 22 Responses to “How to create a unique distinct list based on two conditions”

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

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

your array formula examples are fantastic and incredibly useful, thank you! this one provides almost all the insight I need, except for sorting the result list. I've tried to apply the sorting techniques from some of your other examples and am just not getting it. Can you help?

well after a few hours I think I've figured it out, just need to handle blank rows.

{=IFERROR(INDEX(tbl2,SMALL(IF(SMALL(IF(((($F$2=Color)*($G$2=Texture)*(COUNTIF($I$1:I1,tbl2)=0))),COUNTIF(tbl2,"<"&tbl2)+1,""),1)=COUNTIF(tbl2,"<"&tbl2)+1,ROW(tbl2)-MIN(ROW(tbl2))+1),1)),"")}

where

tbl2 = the original list

'Color' is a range defined on a column next to tbl2, specifying a color for each entry. The same for 'Texture.'

F2 and G2 define the color and texture to be selected, and "I" defines the output column.

Congratulations!

You did it!

Thank you for your contribution!

I modified your named ranges for this post.

Array formula in H2:

=IFERROR(INDEX(List, SMALL(IF(SMALL(IF(((($F$2=Category2)*($F$1=Category1)*(COUNTIF($H$1:H1, List)=0))), COUNTIF(List, "<"&List)+1, ""), 1)=COUNTIF(List, "<"&List)+1, ROW(List)-MIN(ROW(List))+1), 1)), "") + CTRL + SHIFT + ENTER copied down as far as needed.

Oscar this site's been a great help. Having a problem with the above formula for earlier versions though. Should there be semicolons in it?

Andy Green,

Thanks!

No, I have removed the semicolons.

Thanks Oscar. Still getting "Too many arguments for this function.

Thinking about it, it may help if I tell you exactly what I'm trying to do. I'm trying to extract a list of unique values, based on a number of criteria, including some "OR's". Any ideas. I can normally find everything on here but this has me stumped

Andy Green,

It works now, get the attached xls file or copy array formula.

Thanks for letting me know!

Oscar you're a star. You're gonna love me for this....How would I add an "OR" in there. ie Say there was a row such as....

BB C 7

As there's already a Category2"B" I can't add a "+IF(Category2"C", 1, 0)"

So how would I include that one? Any ideas or am I just pushing my luck?

Andy Green,

Named rangesList (C2:C16)

Category1 (A2:A16)

Category2 (B2:B16)

Criteria1 (F1)

Criteria2 (

F2:F3)Excel 2007 array formula in H2:Earlier Excel versions, array formula in H2:You are the Excel Joda!

Yoda quote:

"Feel the (excel) force!"

[...] tables to the web >> Excel Jeanie HTML 4 You can find the above formulas here..... How to create a unique distinct list where other columns meet two criteria | Get Digital Help - Micr... Count unique distinct records with a date and column criteria in excel 2007 | Get Digital Help - [...]

I've solved a similar problem inverting the results (1 and 0) of the "IF" clauses. I post it here in case someone would find it useful:

=INDEX(List; MATCH(0; COUNTIF($H$1:H1; List) + IF(Category>0;0;1) + IF(Category0 and <2)

Hi,

This is absolutely great and it's what I need. I do have one question and maybe it might be the operator (me). I opened the attached file the xlsx version of the file and when I go into the formula to change the range for my data and go out of the cell it returns a blank. If I take out the IFERROR statement it shows a #N/A. I don't understand what I'm doing wrong. Can you help?

Thanks,

Sorry me again. It's the operator with the issue. I didn't change it back to an array formula.

Hi,

Is there a way to make this work on ranges? like, if a cell is larger than 3 and smaller than 5.

I'm sorry, I figured it out now.

I just replaced it with a ">" and "<" so it turns out FALSE and it worked.

Thanks for the code :)

Is there a way to do this without using the IFERROR? The speed of my workbook has ground to a halt after doing this formula. I have 1800 rows of data. Thanks in advance!

Hi, Do you have any idea to show all unique values in one cell

Saravana Kumar,

Yes, this formula works in Excel 365. It is based on the example in this article.

I have a typical query. Given in sequence

1. I have one unique ID for VLOOKUP.

2. Against this Unique ID, there are multiple IDs available and multiple values in text.

3. For example, Unique ID 123456 (Customer Code), there are one more terminals provided with different Merchant ID. Out of this Merchant ID under a single Unique ID, there are various values available like "Approved", "Active", "Hotlisted".

4. Now for a single Unique ID, as explained in 3 above, there are multiple values.

5. I need to consolidate the above with a formula. How to go about and what are the formulae to adopt.

Thank you.