mr t.valko ..help
 

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

mr t.valko ..help
 

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

mr t.valko ..help
 

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

mr t.valko ..help
 

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

mr t.valko ..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

mr t.valko ..help
 

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

mr t.valko ..help
 

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

mr t.valko ..help
 

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

mr t.valko ..help
 

You're welcome!

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...

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