I'm not entirely clear on all the different permutations you're looking for,
but I think you can create a pivot table that will show the answers very
quickly. At least give it a look...
On sheet2, click in cell A1 (I'm assuming you've got a nicely organized
table with column headers in row 1 and no blanks in your table). From the
menu bar, select Data Pivot Table... Click Next, Next, Layout. Drag the
'Customer' button to 'Row'. Drag the 'Product' button to 'Column' AND to
'Data'. Click OK and then Finish. Now there's a table on a new worksheet,
and you can easily see all the combinations of customer and product, so you
can tell which customers have no products, etc.

"Calculate Date range" wrote:

Hello all,

I have spent half day to determine on how to set the following formula but
turn out i got nothing

In Excel, I have product name in one spreadsheet

Product Name ( spreadsheet 1)
cell (A1)
ABC
EDF
JIG
KAG
LNN
POR

Spreadsheet 2, I have customer name about 2200 rows:-

Cell (A1) Cell (B1)
Customer name Product name
A EDF
A JIG
A KAG
A LNN
B JIG
B KAG
B LNN
B POR

How to set a formula tin Cell A3 to determine Customer A haven't buy product
'ABC" & "POR" and Customer B haven't buy product 'ABC" & "EDF"

In some situation, customer may bought all the 6 products.

thanks.