Bob,
I tried your formula (as part of my ongoing education programme)
but I don't get the expected results so what (if anything is wrong). The data
below shows the results (left hand column) and the right hand side shows the
data on Sheet1. So for example, I would have expected to see Customer 14 in
the results list.

(Usual alignment problems but hopefully you can see how the data is arranged).

Results customer 10 YES
customer 10 customer 14 YES
customer 16 customer 16 No
customer 26 customer 23 YES
customer 30 customer 24 YES
customer 39 customer 26 No
customer 50 customer 3 No
customer 9 customer 30 No
customer 31 No
customer 32 No
customer 33 YES
customer 34 No
customer 35 No
customer 38 YES
customer 39 YES
customer 45 YES
customer 48 No
customer 49 No
customer 50 YES
customer 8 YES
customer 9 YES


"Bob Phillips" wrote:

On the second sheet, select all of your target cells for the company name
(probably A2:A500, A1 for the heading)and add this to the formula bar

=IF(ISERROR(SMALL(IF(Sheet1!$H$1:$H$500="Yes",ROW( $A1:$A$500),""),ROW($A1:$A
$500))),"",
INDEX(Sheet1!A$1:A$500,SMALL(IF(Sheet1!$H$1:$H$500 ="Yes",ROW($A1:$A$500),"")
,ROW($A1:$A$500))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

Copy the block formula acroos to column B to get the product.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"EdMac" wrote in
message ...

I have a workbook that includes 4 sheets based on quarters of the year
and a summary page.

In A1:A500 is an alpha listing of company names - there may be
duplicates.
In B1:B500 are the products supplied by the companies.

In H1:h500 yes/No is selected if there are quality concerns.
J1:J500 details the concern if Yes is selected in H1:H500.

I would like to be able to produce a (in the summary page) a summary of
the the companies and products that have quality concerns. The same
company may appear more than once, the product is unique.

Is it possible to achieve this without VB coding? I seem to remember a
similar question sometime ago but have been unable to locate it.

Any assistance gratefully received

EdMac


--
EdMac
------------------------------------------------------------------------
EdMac's Profile:
http://www.excelforum.com/member.php...o&userid=30736
View this thread: http://www.excelforum.com/showthread...hreadid=558845