Creating a list without duplicates or array formula

Hi,

May I ask why you can't use an array formula?

In I6 and copy across and down:

=MAX(INDEX(($E$6:$E$34=I$5)*(1-ISNUMBER(MATCH($F$6:$F$34,I$5:I5,0)))*$F$6:$F$34,,))

Regards

Slght improvement:

=IF(ROWS($1:1)>=SUMPRODUCT(0+(FREQUENCY(($E$6:$E$34=I$5)*$F$6:$F$34,$F$6:$F$34)>0)),"",MAX(INDEX(($E$6:$E$34=I$5)*(1-ISNUMBER(MATCH($F$6:$F$34,I$5:I5,0)))*$F$6:$F$34,,)))

though this would be far more concise with an array formula!

Regards

Firstly, what you about array formulas is not correct.

Secondly, why do you have all those gaps in your data? Is this unavoidable? It certainly makes things more complex, so suggest you first clean up your data then re-attach.

Regards

Which version of Excel are you using? Your profile says 2003 but you posted an xlsx file which indicates 2007 or later.

If you have Excel 2010 you can use AGGREGATE function, e.g. for your original data use this ordinary (non-array) formula in I6 copied across and down

=AGGREGATE(14,6,($E$6:$E$34=I$5)*($F$6:$F$34<I5)*$F$6:$F$34,1)

when you run out of values you get zeroes, custom format as 0;; to show those as blanks

There were still some # values in the data. If you take those out and delete row 2 of the results table then you can use this regular formula in F3 copied across and down

=MAX(INDEX(($B$3:$B$3000=F$2)*($C$3:$C$3000<F2)*$C$3:$C$3000,0))

That one will work in Excel 2007, see attached

Note: if you want to keep the # values in the data then this "array formula" will give you the same results while ignoring non-numeric data

=MAX(IF(ISNUMBER($C$3:$C$3000),IF(($B$3:$B$3000=F$2)*($C$3:$C$3000<F2),$C$3:$C$3000)))

confirmed with CTRL+SHIFT+ENTER

Based on your last post in this thread, its apparent that you are satisfied with the solution(s) you've received and have solved your question, but you haven't marked your thread as "SOLVED". I will do it for you this time.

In future, to mark your thread as Solved, you can do the following -
Select Thread Tools-> Mark thread as Solved.

Incase your issue is not solved, you can undo it as follows -
Select Thread Tools-> Mark thread as Unsolved.

Also, since you are relatively new to the forum, i would like to inform you that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post which helped you. This adds to the reputation of the person who has taken the time to help you.

**Deleted**