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:
Recommended reading
Create a unique distinct list based on criteria
Question: I want a unique list to be created from a column where an adjacent column has cell values between [โฆ]
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.
Learn the basics of Excel arrays
Array formulas allows you to do advanced calculations not possible with regular formulas.
Copy cell H2. Paste down to cell H16.
Earlier Excel versions, array formula in H2:
Copy cell H2. Paste down to cell H16.
Recommended reading:
Create a unique distinct list where a corresponding column has text cell values
Question: I want a unique list to be created from a column where an adjacent column has text cell value? [โฆ]
Named ranges
List (C2:C16)
Category1 (A2:A16)
Category2 (B2:B16)
Criteria1 (F1)
Criteria2 (F2)
Recommended reading:
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 [โฆ]
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}.
How to use the COUNTIF function
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}.
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 [โฆ]
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)
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 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 from a filtered table [udf and array formula]
Robert Jr asks: Oscar, I am using the VBA code & FilterUniqueSort array to generate unique lists that drive Selection [โฆ]19 Responses to โHow to create a unique distinct list where other columns meet two criteriaโ
Leave a Reply
How to add a formula to your comment:
<code>your formula</code>
Remember to convert less than and larger than signs to html character entities before you post your comment.
How to add VBA code to your comment:
[vb 1="vbnet" language=","]
VBA 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
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!