Modify Index/Match Formula to Exclude Duplicates

[nau2002]

Hello,

I've tried to modify an existing array formula that returns a list of all Projects meeting the "Yes" value on another sheet (Input) to exclude any lines that are duplicate. I can't get seem to get the modified formula right with regard to parenthesis, # of arguments, etc. Any help would be greatly appreciated!

=IFERROR(INDEX(Input!$F$4:$F$201,SMALL(IF(N(FREQUENCY(IF(Input!$H$4:$H$201="Yes",IF(ISNUMBER(MATCH(Input!$A$4:$A$201,$A$4,0)),MATCH(Input!$A$4:$A$201,$A$4,0)))>0)>0,ROW(Input!$A$4:$A$201)-ROW(Input!$4:$4)+1)),ROWS($A5:$A$5))),"")

And here was my original formula which worked except picked up duplicate lines.
=IFERROR(INDEX(Input!$F$4:$F$201,SMALL(IF(Input!$H$4:$H$201="Yes",IF(ISNUMBER(MATCH(Input!$A$4:$A$201,$A$4,0)),ROW(Input!$A$4:$A$201)-ROW(Input!$4:$4)+1)),ROWS($A5:$A$5))),"")

 

[FDibbins]

maybe to keep this simple, add a helper column and use COUNTIF() to count the dups, then build that into your index/small/if to only look at 1's in the helper?

assuming your helper is in Z....
=IFERROR(INDEX(Input!$F:$F,SMALL(IF((Input!$H$4:$H$201="Yes")*(Input!$z$4:$z$201=1),IF(ISNUMBER(MATCH(Input!$A$4:$A$201,$A$4,0)),ROW(Input!$A$4:$A$201)),ROWS($A$5:$A5))),"")

 

[ketxu]

Could you post your workbook ?

 

[Aladin Akyurek]

Hello,

I've tried to modify an existing array formula that returns a list of all Projects meeting the "Yes" value on another sheet (Input) to exclude any lines that are duplicate. I can't get seem to get the modified formula right with regard to parenthesis, # of arguments, etc. Any help would be greatly appreciated!

=IFERROR(INDEX(Input!$F$4:$F$201,SMALL(IF(N(FREQUENCY(IF(Input!$H$4:$H$201="Yes",IF(ISNUMBER(MATCH(Input!$A$4:$A$201,$A$4,0)),MATCH(Input!$A$4:$A$201,$A$4,0)))>0)>0,ROW(Input!$A$4:$A$201)-ROW(Input!$4:$4)+1)),ROWS($A5:$A$5))),"")

And here was my original formula which worked except picked up duplicate lines.
=IFERROR(INDEX(Input!$F$4:$F$201,SMALL(IF(Input!$H$4:$H$201="Yes",IF(ISNUMBER(MATCH(Input!$A$4:$A$201,$A$4,0)),ROW(Input!$A$4:$A$201)-ROW(Input!$4:$4)+1)),ROWS($A5:$A$5))),"")

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Input!$F$4:$F$201,SMALL(IF(FREQUENCY(IF(1-(Input!$F$4:$F$201=""),IF(Input!$H$4:$H$201="Yes",IF(Input!$A$4:$A$201=$A$4,MATCH(Input!$F$4:$F$201,Input!$F$4:$F$201,0)))),ROW(Input!$F$4:$F$201)-ROW(Input!$F$4)+1),ROW(Input!$F$4:$F$201)-ROW(Input!$F$4)+1),ROWS($A$5:A5))),"")

 

[nau2002]

I used the option posted by Aladin and it worked great. Thanks to all for the feedback!!