Yuck!

You'll be doing yourself (and others) a great benefit by dropping your use
of INDIRECT so much. It isn't efficient at all and it's not necessary.

With a formula like that it's better to not use an error trap and just use
conditional formatting to hide them. Or, use a helper cell to get the count
of uniques that meet the criteria and then refer to that cell like this:

=IF(ROWS($1:1)<=A$1,INDEX(........................ ..),"")

It's more efficient and makes the formula smaller.

Using your formula as is, is nearly 7.5 times slower (on average) on a
recalculation (when the workbook calculates)

Biff

"Teethless mama" wrote in message
...
Try this:

=IF(ISERR(SMALL(IF(FREQUENCY(IF($A$2:$A$12="AP",MA TCH($B$2:$B$12,$B$2:$B$12,0)),MATCH($B$2:$B$12,$B$ 2:$B$12,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$12)))), ROWS($1:1))),"",INDEX($B$2:$B$12,SMALL(IF(FREQUENC Y(IF($A$2:$A$12="AP",MATCH($B$2:$B$12,$B$2:$B$12,0 )),MATCH($B$2:$B$12,$B$2:$B$12,0)),ROW(INDIRECT("1 :"&ROWS($B$2:$B$12)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
Drag the Fill Handle to copy as far as needed


"SSJ" wrote:

Hello everyone!

I would like to extract unique records based on a condition. For example,
how to extract unique record from column 'B' when column 'A' has "AP" or
any other desired condition.

The data is as follows:

A B
MI 70056542
MI 70056543
AP PATRICK CUDAHY INCORPORATED
AP PATRICK CUDAHY INCORPORATED
AP SUGAR CREEK PACKING CO
AP SUGAR CREEK PACKING CO
AP VICTORYS KITCHEN
AP VICTORYS KITCHEN
AP BRIGHT CHEESE HOUSE
AP BRIGHT CHEESE HOUSE
AP CAPPOLA FOODS INC


The final result should look like this:

C
PATRICK CUDAHY INCORPORATED
SUGAR CREEK PACKING CO
VICTORYS KITCHEN
BRIGHT CHEESE HOUSE
CAPPOLA FOODS INC


Thanks
SJ