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
If the value argument returns an error, the value_if_error argument is used. If the value argument does NOT return an error, the IFERROR function [โฆ]
How to use the IFERROR function
Download 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 [โฆ]
Create a unique distinct alphabetically sorted list
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 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 a unique distinct list and remove blanks
Question: How do I create a unique distinct list from a list containing several blanks? Answer: Cell range B3:B12 contains several [โฆ]
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 [โฆ]
19 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, download 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 ranges
List (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 download the xlsx version of the file and when I click into the formula to change the range for my data and click 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!