How to create a unique distinct list where other columns meet two criteria
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
How to implement array formula to your workbook
Change named ranges. If your unique distinct list starts at, for example, F3. Change H1:$H$1 in the above formula to F2:$F$2. The ranges don´t have to be adjacent.
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}.
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}.
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), "")
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, "")
Step 6 - Check if expression returns an error
=IFERROR(4, "")
returns number 4 in cell H2.
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)
Functions in this article:
MATCH(lookup_value;lookup_array; [match_type]
Returns the relative position of an item in an array that matches a specified value
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
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
COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition
This blog article is one out of thirteen articles on the same subject "unique".
- How to extract a unique distinct list from a column in excel
- Extract a unique distinct list from two columns using excel 2007 array formula
- Extract a unique distinct list from three columns in excel
- Extract distinct unique sorted year and month list from a date series in excel
- Create a unique distinct list from a date range in excel
- Unique values from multiple columns using array formulas
- Extract a unique distinct list sorted from A-Z from range in excel
- Sort a range by occurence using array formula in excel
- Filter unique distinct values from two ranges combined in excel 2007
- Create a unique list and sort by occurrances from large to small
- Unique list to be created from a column where an adjacent column has text cell values
- Create unique list from column where an adjacent column meets criteria
- How to create a unique distinct list where other columns meet two criteria
Related posts:
Extract a unique distinct list from three columns in excel
Create unique list from two columns
Extract a unique distinct list from two columns using excel 2007 array formula
Create unique distinct list from column where an adjacent column meets criteria
Extract a unique distinct list by matching items that meet a criterion in excel
Recommended categories
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 - [...]