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 ??

Try these edited versions of the array formulas you posted....

C1: =SUMPRODUCT(COUNTIF(A1:A10,"*"&IF(B1:B3<"",B1:B3, "skip")&"*"))

C2: =IF(ROWS(C$2:C2)<=C$1,INDEX(A$1:A$10,SMALL(
IF(ISNUMBER(SEARCH(TRANSPOSE(IF(B$1:B$3<"",B$1:B$ 3)),
A$1:A$10)),ROW(A$1:A$10)),ROWS(C$2:C2))),"")

Copy C2 into C3 and down as far as you need.

Note: Blanks in the B1:B3 range must be UNDER the criteria text.

Also, remember to use CTRL+SHIFT+ENTER, instead of ENTER.

Does that help?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel


"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 ??

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 ??

thank you guys it worked ....BUT i have another ISSUE please help :

given :

C
1 tea
2 tree
3 tree
4 tea
5 -

i am entering in cell E1 :SUMPRODUCT(COUNTIF(c1:c5;D1:D3))

and after that i am entering in cell E2 :
{=IF(ROWS(E$2:E2)<=E$1;ADDRESS(INDEX(ROW($c1$:$c5$ );SMALL(IF($c1$:$c5$=TRANSPOSE($D$1:$D$2);ROW($c1$ :$c5$));ROWS(E$2:E2)));COLUMN($c1$:$c5$);4);"")}

and i dragg down.
MY PROBLEM IS THAT WHEN I ENTER THE WORD "tea" IN SEARCH STRING
CELL D1 , AND THE WORD "tree" IN SEARCH STRING CELL D2
WHILE LEAVING SEARCH STRING CELL D3 EMPTY... i get unwanted results
ALTHOUGH I SHOUD GET : C1 , C2 , C3 , C4 , C5

PLEASE HELP

It works OK for me.

One thing, you have the references to C1:C5 typed incorrectly: $c1$:$c5$.
I'll assume those are just typos.

I SHOUD GET : C1 , C2 , C3 , C4 , C5

No, you should get (and I do) C1, C2, C3, C4. C5 is empty.

You can reduce this: COLUMN($c1$:$c5$)

To:

COLUMN($C1)


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...

thank you guys it worked ....BUT i have another ISSUE please help :

given :

C
1 tea
2 tree
3 tree
4 tea
5 -

i am entering in cell E1 :SUMPRODUCT(COUNTIF(c1:c5;D1:D3))

and after that i am entering in cell E2 :
{=IF(ROWS(E$2:E2)<=E$1;ADDRESS(INDEX(ROW($c1$:$c5$ );SMALL(IF($c1$:$c5$=TRANSPOSE($D$1:$D$2);ROW($c1$ :$c5$));ROWS(E$2:E2)));COLUMN($c1$:$c5$);4);"")}

and i dragg down.
MY PROBLEM IS THAT WHEN I ENTER THE WORD "tea" IN SEARCH STRING
CELL D1 , AND THE WORD "tree" IN SEARCH STRING CELL D2
WHILE LEAVING SEARCH STRING CELL D3 EMPTY... i get unwanted results
ALTHOUGH I SHOUD GET : C1 , C2 , C3 , C4 , C5

PLEASE HELP

is there a way to transform the results : C1 , C2 , C3 , C4
into hyperlinks in order to go to their correspondent given ??

If I were you I'd use another cell.

Enter this formula in F2 and copy down as needed:

=IF(E2="","",HYPERLINK("#"&E2,"Go to: "&E2))

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
is there a way to transform the results : C1 , C2 , C3 , C4
into hyperlinks in order to go to their correspondent given ??

THANK YOU VERY MUCH..........thanks for you time and for these precious
answers

You're welcome!

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...

THANK YOU VERY MUCH..........thanks for you time and for these precious
answers