List Unique Values Based On Criteria

Hi.

In G1, array formula**:

=IFERROR(SMALL(IF(FREQUENCY(IF($D$2:$D$9=$A$1,$E$2:$E$9),$E$2:$E$9),$E$2:$E$9),COLUMNS($A:A)),"")

Copy across as required.

I used a rather lazy IFERROR approach since it appears that you do not have a very large dataset.

Regards


**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

No problems, but in that case shouldn't you be arranging to have these values returned into a single column, rather than a single row?

Regards

Then the most efficient method would be to put a single, non-array formula in e.g. F1, used to determine the expected number of returns:

=SUMPRODUCT((D2:D9=A1)/COUNTIF(E2:E9,E2:E9))

(Assumes there are no blanks in the range, just as in your attached example.)

Your array formula** in G1 is then:

=IF(COLUMNS($A:A)>$F$1,"",SMALL(IF(FREQUENCY(IF($D$2:$D$9=$A$1,$E$2:$E$9),$E$2:$E$9),$E$2:$E$9),COLUMNS($A:A)))

and copied to the right.

Regards