Eliminate duplicates in a dynamic list

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
91
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,750
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
Joined
Dec 11, 2014
Messages
91
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,750
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.
 
Solution

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
91
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Aug 18, 2015
Messages
10,750
There you go! I knew it had to be something small. Glad you got it working! Thanks for the feedback! :cool:
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top