mr t.valko ..help

[pierre]

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

 

[Ron Coderre]

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

 

[T. Valko]

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 Insert>Name>Define
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(ISNUMBER(SEARCH(TRANSPOSE(Strings),A$1:A$10)),ROW(A$1:A$10)),ROWS(C$2:C2))),"")

 

[pierre]

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;D13))

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

 

[T. Valko]

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)

 

[pierre]

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

 

[T. Valko]

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

 

[pierre]

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

 

[T. Valko]

You're welcome!