INDEX,SMALL,ROW - Ignore Duplicate Values

[RobMMiller]

Hi,

I was wondering if you can help. I have a list of words on a tab called TabellePositionHistory in Column D. I want to reproduce this list on another tab if the content of column F and G on the same row are greater than 0. The below formula does this perfectly:

=(IF(ISERROR(INDEX(TabellePositionHistory!$D$1:$AB$500,SMALL(IF(TabellePositionHistory!$AB$1:$AB$500>0,ROW(TabellePositionHistory!$AB$1:$AB$500)),ROW(7:7)),1)),"",INDEX(TabellePositionHistory!$D$1:$AB$500,SMALL(IF(TabellePositionHistory!$AB$1:$AB$500>0,ROW(TabellePositionHistory!$AB$1:$AB$500)),ROW(7:7)),1)& " Equity"))

However, I now need to make sure that it doesn't produce duplicates in the results, from what I can see I need to add a MATCH or COUNTIF function...but don't know how. Thanks in advance.

Rob

 

[istiasztalos]

Hi,

There's an easier formula to get unique values from a list, it can also be modified to look at multiple criteria. This one takes unique values from a range "mylist" ( this can be your column D in TabellePositionHistory ) only if the respective lines in column G and F are > 0 (these two columns are here named ranges as "fcol" and "gcol")

=IFERROR(INDEX(mylist,MATCH(0&1,(COUNTIF(J$1:J1,mylist))&((fcol>0)*(gcol>0)),0)),"")

J1 is the cell above the one where you enter this.

Enter this formula with CTRL+SHIFT+ENTER (Excel will put {} around it ) and drag down.