Create a unique list of names based on multiple criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2365266%22%20slang%3D%22en-US%22%3ECreate%20a%20unique%20list%20of%20names%20based%20on%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365266%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there!%20I%20currently%20have%20a%20master%20list%20that%20has%20names%2C%20organizations%20and%20whether%20or%20not%20they%20attended%20a%20workshop.%26nbsp%3B%20I%20need%20to%20create%20a%20list%20for%20each%20agency%20that%20lists%20the%20name%20of%20the%20individuals%2C%20but%20only%20when%20they%20have%20attended%20a%20workshop.%26nbsp%3B%20I%20tried%20to%20create%20the%20list%20using%20the%20formula%20based%20off%20this%20page%3A%20%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Funique-values-with-multiple-criteria%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Funique-values-with-multiple-criteria%3C%2FA%3E%26nbsp%3B%20but%20I%20keep%20getting%20a%20%23calc%20error%20message.%26nbsp%3B%20The%20formula%20works%20if%20I%20only%20list%20one%20criteria%20though.%26nbsp%3B%20Thoughts%3F%20Would%20an%20if%20formula%20work%20better%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2365266%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2365351%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20unique%20list%20of%20names%20based%20on%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365351%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F226155%22%20target%3D%22_blank%22%3E%40Sarah%20Smith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20attach%20a%20small%20sample%20workbook%20(without%20sensitive%20information)%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2365582%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20unique%20list%20of%20names%20based%20on%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365582%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%20I%20just%20updated%20the%20post%20with%20a%20sample%20excel%20file%20so%20you%20can%20see%20what%20type%20of%20a%20document%20I%20was%20working%20with%20and%20how%20I%20set%20up%20the%20formula%20as%20well.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2365326%22%20slang%3D%22en-US%22%3ERe%3A%20Create%20a%20unique%20list%20of%20names%20based%20on%20multiple%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2365326%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F226155%22%20target%3D%22_blank%22%3E%40Sarah%20Smith%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3CSPAN%20class%3D%22sy1%22%3E%3D%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-unique-function%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3E%3CSPAN%20class%3D%22kw4%22%3EUNIQUE%3C%2FSPAN%3E%3C%2FA%3E%3CSPAN%20class%3D%22sy0%22%3E(%3C%2FSPAN%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fexcel-functions%2Fexcel-filter-function%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noreferrer%22%3E%3CSPAN%20class%3D%22kw4%22%3EFILTER%3C%2FSPAN%3E%3C%2FA%3E%3CSPAN%20class%3D%22sy0%22%3E(%3C%2FSPAN%3Edata%3CSPAN%20class%3D%22sy0%22%3E%2C(%3C%2FSPAN%3Erange1%3CSPAN%20class%3D%22sy1%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22st0%22%3E%22b%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22sy0%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22sy1%22%3E*%3C%2FSPAN%3E%3CSPAN%20class%3D%22sy0%22%3E(%3C%2FSPAN%3Erange2%3CSPAN%20class%3D%22sy1%22%3E%26gt%3B%3C%2FSPAN%3E%3CSPAN%20class%3D%22nu0%22%3E5%3C%2FSPAN%3E%3CSPAN%20class%3D%22sy0%22%3E)%2C%22no%20match%20found%22))%3C%2FSPAN%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi there! I currently have a master list that has names, organizations and whether or not they attended a workshop.  I need to create a list for each agency that lists the name of the individuals, but only when they have attended a workshop.  I tried to create the list using the formula based off this page: https://exceljet.net/formula/unique-values-with-multiple-criteria  but I keep getting a #calc error message.  The formula works if I only list one criteria though.  Thoughts? Would an if formula work better?

4 Replies

@Sarah Smith 

=UNIQUE(FILTER(data,(range1="b")*(range2>5),"no match found"))

@Sarah Smith 

Could you attach a small sample workbook (without sensitive information)?

@Hans Vogelaar I just updated the post with a sample excel file so you can see what type of a document I was working with and how I set up the formula as well.

@Sarah Smith 

Thank you! The values in columns C and D are numbers, not text values, so you shouldn't use quotes around values such as 1. The formula in M4 should be

 

=UNIQUE(FILTER($A$2:$A$5,($B$2:$B$5=M3)*($C$2:$C$5=1)))

 

This can be filled to the right to N4. Note that I made the references to A2:A5, B2:B5 and C2:C5 absolute so that they don't change when you fill to the right.