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}.
Recommended articles
Counts the number of cells that meet a specific condition.
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}.
Recommended articles
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), "")
Recommended articles
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, "")
Recommended articles
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.
Recommended articles
The IFERROR function lets you catch most errors in Excel formulas. It was introduced in Excel 2007. In previous Excel […]
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)
Unique distinct values category
First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]
Question: I have two ranges or lists (List1 and List2) from where I would like to extract a unique distinct […]
This article shows how to extract unique distinct values based on a condition applied to an adjacent column using formulas. […]
Excel categories
23 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.
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 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 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.
Hello Yoda,
is it possible to have this formula with 3 criteria? i tried but... :)