Eliminate duplicates in a dynamic list

excelbytes

Board Regular
I use this formula structure or a variation of it to extract a dynamic list from a data range or table:

=IFERROR(INDEX(\$A\$2:\$A\$26,SMALL(IF((\$B\$2:\$B\$26=\$H\$1)*(\$C\$2:\$C\$26>=\$I\$1),ROW(\$A\$2:\$A\$26)),ROW(1:1))-1,1),"")

The question has come up, how to ensure the results have no duplicates. I know we can add a helper column to the data range that would include a COUNTIF function and then add a criteria where the value is only 1, but is there a way to accomplish this within the formula itself?

Thanks.

Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Eric W

MrExcel MVP
If you have the new array functions, then just:

Excel Formula:
``=UNIQUE(FILTER(A2:A26,(B2:B26=H1)*(C2:C26>=I1)))``

If not, and your formula is in F2, then:

Excel Formula:
``=IFERROR(INDEX(\$A\$2:\$A\$26,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$26)-ROW(\$A\$2)+1)/(\$B\$2:\$B\$26=\$H\$1)/(\$C\$2:\$C\$26>=\$I\$1)/(COUNTIF(\$F\$1:\$F1,\$A\$2:\$A\$26)=0),1)),"")``

excelbytes

Board Regular
Eric W,

Thanks. Yes, it's much simpler with the UNIQUE and FILTER functions, however, not all have that option. I did try your other formula and it resulted in the first instance being repeated for the entire list.

Eric W

MrExcel MVP
Here is my test sheet:

Dynamic functions.xlsm
ABCDEFGHI
1ListCodeAmtMatching listx10
2ax11a
3b12c
4cx13d
5dx14
6e15
7f16
8ax17
9b18
10c19
11
Sheet4
Cell Formulas
RangeFormula
F2:F8F2=IFERROR(INDEX(\$A\$2:\$A\$26,AGGREGATE(15,6,(ROW(\$A\$2:\$A\$26)-ROW(\$A\$2)+1)/(\$B\$2:\$B\$26=\$H\$1)/(\$C\$2:\$C\$26>=\$I\$1)/(COUNTIF(\$F\$1:\$F1,\$A\$2:\$A\$26)=0),1)),"")

If you're getting the same value repeated, then that's an issue with the COUNTIF function. Notice the \$F\$1:\$F1 in the formula. There is no \$ in front of the second 1, so as you drag the formula down, the range gets larger. So the COUNTIF looks at all the values above the current row to see if the value has been used before.

excelbytes

Board Regular
Your formula works, but it wasn't the issue concerning the mixed cell reference in the COUNTIF function. It was that my results column is E and your formula referenced column F. So when I made that change, it worked. Thanks!

Eric W

MrExcel MVP
There you go! I knew it had to be something small. Glad you got it working! Thanks for the feedback!

Replies
0
Views
31
Replies
11
Views
529
Replies
2
Views
113
Replies
2
Views
136
Replies
2
Views
76

1,127,444
Messages
5,624,811
Members
416,056
Latest member
VARSHA V VASWANI

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back