The problem is the empty cell B3.

What you could do to retain the dynamic properties of this process is use a
dynamic range for the search strings in B1:Bn.

Goto InsertNameDefine
Name:Strings
Refers to: (use your actual sheet name)

=Sheet1!$B$1:INDEX(Sheet1!$B$1:$B$10,COUNTA(Sheet1 !$B$1:$B$10))

OK

Now, change the formula in C1 to:

=SUMPRODUCT(COUNTIF(A1:A10,"*"&Strings&"*"))

Change the array formula** in C2 to:

=IF(ROWS(C$2:C2)<=C$1,INDEX(A$1:A$10,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(Strings),A$1:A$10)),ROW(A$1: A$10)),ROWS(C$2:C2))),"")


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
hello

i have the following data :

A
1 axe red
2 axe blue
3 axe green
4 axe pink
5 table a
6 table b
7 table c
8 -
9 -
10 -

i am entering in cell C1 : =SUMPRODUCT(COUNTIF(A1:A10;"*"&B1:B3&"*"))
and after that i am entering in cell C2 :
{=IF(ROWS(C$2:C2)<=C$1;INDEX(A$1:A$10;SMALL(IF(ISN UMBER(SEARCH(TRANSPOSE(B$1:B$3);A$1:A$10));ROW(A$1 :A$10));ROWS(C$2:C2)));"")}
and i dragg down.

MY PROBLEM IS THAT WHEN I ENTER THE WORD "AXE" IN SEARCH STRING
CELL B1 , AND THE WORD "TABLE" IN SEARCH STRING CELL B2
WHILE LEAVING SEARCH STRING CELL B3 EMPTY....I GET RESULTS AS DUPLICATES
FROM CELL C2 TO CELL C20 LIKE FOLLOWS :

axe red
axe red
axe blue
axe blue
axe green
axe green
axe pink
axe pink
table a
table a
table b
table b
table c
table c

WHAT SHOULD I DO ??