ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   TO mr t.valko... (https://www.excelbanter.com/excel-discussion-misc-queries/189401-mr-t-valko.html)

Pierre
TO mr t.valko...
 
given :

A B
1 tree a 10
2 tree b 20
3 tree b 30
4 axe a 40
5 axe b 50
6 axe c 60

my search string cells are : c1 : c3

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

in cell D2 i entered :
{=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$7;SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF(C$1:C$3<"",C$1:C$3)),A$1 :A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")}
and i dragged down...


my problem is : WHAT FORMULA SHOULD I ENTER in cell E2 IN ORDER TO GET THE
CORRESPONDING VALUES of "tree" and "axe" ???

i tried the following :

i enterd in E1 :SUMPRODUCT(COUNTIF(D2:D12,"*"))
and i entered in E1:
{=IF(ROWS(E$2:E2)<=E$1,INDEX(B$1:B$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($B$1:$B$7<"",$B$1:$B$7)) ,B$1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E2))),"")}
and i dragged down

now if i enter the word "axe" in cell C1 i get the following WRONG
results

10 for axe a
20 for axe b
30 for axe c

HELP

Pierre
TO mr t.valko...
 
i managed to reach to this formula :

{=INDEX(B$1:B$7;SMALL(IF($A$1:$A$7=TRANSPOSE(IF($D $2:D2<"";$D$2:D2));ROW(B$1:B$7));ROWS(B$1:B1)))}

but my problem is now that if i enter "tree" in C1 i get duplicate numbers
like follows :

tree a 10 (good answer)
tree b 20 (good answer)
tree b 20 ---- wrong answer : IT SHOULD BE 30

ANY SUGGESTIONS ?????






"pierre" wrote:

given :

A B
1 tree a 10
2 tree b 20
3 tree b 30
4 axe a 40
5 axe b 50
6 axe c 60

my search string cells are : c1 : c3

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

in cell D2 i entered :
{=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$7;SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF(C$1:C$3<"",C$1:C$3)),A$1 :A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")}
and i dragged down...


my problem is : WHAT FORMULA SHOULD I ENTER in cell E2 IN ORDER TO GET THE
CORRESPONDING VALUES of "tree" and "axe" ???

i tried the following :

i enterd in E1 :SUMPRODUCT(COUNTIF(D2:D12,"*"))
and i entered in E1:
{=IF(ROWS(E$2:E2)<=E$1,INDEX(B$1:B$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($B$1:$B$7<"",$B$1:$B$7)) ,B$1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E2))),"")}
and i dragged down

now if i enter the word "axe" in cell C1 i get the following WRONG
results

10 for axe a
20 for axe b
30 for axe c

HELP

T. Valko
TO mr t.valko...
 
in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

That formula needs to be array entered.

Enter this array formula in D2 and copy across to E2:

=IF(ROWS(D$2:D2)<=$D$1,INDEX(A$1:A$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<"",$C$1:$C$3)) ,$A$1:$A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")

Select both D2 and E2 then copy down as needed.


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
given :

A B
1 tree a 10
2 tree b 20
3 tree b 30
4 axe a 40
5 axe b 50
6 axe c 60

my search string cells are : c1 : c3

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

in cell D2 i entered :
{=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$7;SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF(C$1:C$3<"",C$1:C$3)),A$1 :A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")}
and i dragged down...


my problem is : WHAT FORMULA SHOULD I ENTER in cell E2 IN ORDER TO GET THE
CORRESPONDING VALUES of "tree" and "axe" ???

i tried the following :

i enterd in E1 :SUMPRODUCT(COUNTIF(D2:D12,"*"))
and i entered in E1:
{=IF(ROWS(E$2:E2)<=E$1,INDEX(B$1:B$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($B$1:$B$7<"",$B$1:$B$7)) ,B$1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E2))),"")}
and i dragged down

now if i enter the word "axe" in cell C1 i get the following WRONG
results

10 for axe a
20 for axe b
30 for axe c

HELP



Pierre
TO mr t.valko...
 
i get empty cells........there is something wrong







"T. Valko" wrote:

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

That formula needs to be array entered.

Enter this array formula in D2 and copy across to E2:

=IF(ROWS(D$2:D2)<=$D$1,INDEX(A$1:A$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<"",$C$1:$C$3)) ,$A$1:$A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")

Select both D2 and E2 then copy down as needed.


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
given :

A B
1 tree a 10
2 tree b 20
3 tree b 30
4 axe a 40
5 axe b 50
6 axe c 60

my search string cells are : c1 : c3

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

in cell D2 i entered :
{=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$7;SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF(C$1:C$3<"",C$1:C$3)),A$1 :A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")}
and i dragged down...


my problem is : WHAT FORMULA SHOULD I ENTER in cell E2 IN ORDER TO GET THE
CORRESPONDING VALUES of "tree" and "axe" ???

i tried the following :

i enterd in E1 :SUMPRODUCT(COUNTIF(D2:D12,"*"))
and i entered in E1:
{=IF(ROWS(E$2:E2)<=E$1,INDEX(B$1:B$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($B$1:$B$7<"",$B$1:$B$7)) ,B$1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E2))),"")}
and i dragged down

now if i enter the word "axe" in cell C1 i get the following WRONG
results

10 for axe a
20 for axe b
30 for axe c

HELP




T. Valko
TO mr t.valko...
 
The formula does work.

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
i get empty cells........there is something wrong







"T. Valko" wrote:

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

That formula needs to be array entered.

Enter this array formula in D2 and copy across to E2:

=IF(ROWS(D$2:D2)<=$D$1,INDEX(A$1:A$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<"",$C$1:$C$3)) ,$A$1:$A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")

Select both D2 and E2 then copy down as needed.


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
given :

A B
1 tree a 10
2 tree b 20
3 tree b 30
4 axe a 40
5 axe b 50
6 axe c 60

my search string cells are : c1 : c3

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

in cell D2 i entered :
{=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$7;SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF(C$1:C$3<"",C$1:C$3)),A$1 :A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")}
and i dragged down...


my problem is : WHAT FORMULA SHOULD I ENTER in cell E2 IN ORDER TO GET
THE
CORRESPONDING VALUES of "tree" and "axe" ???

i tried the following :

i enterd in E1 :SUMPRODUCT(COUNTIF(D2:D12,"*"))
and i entered in E1:
{=IF(ROWS(E$2:E2)<=E$1,INDEX(B$1:B$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($B$1:$B$7<"",$B$1:$B$7)) ,B$1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E2))),"")}
and i dragged down

now if i enter the word "axe" in cell C1 i get the following
WRONG
results

10 for axe a
20 for axe b
30 for axe c

HELP

Pierre
TO mr t.valko...
 
yes it worked ....THANK YOU

i have another issue.....please:

A B C D
1 a 10 2 c 40
2 b 20 3 d 50
3 b 30 4 e 60

I would like to use index(€¦(match)) function in cell B10 and that in order
to be able to enter : a,b,€¦e,f in search string cell A10 ,So as to get
their corresponding results
how to do that ?? Or do you have other suggestions ??





"T. Valko" wrote:

The formula does work.

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
i get empty cells........there is something wrong







"T. Valko" wrote:

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

That formula needs to be array entered.

Enter this array formula in D2 and copy across to E2:

=IF(ROWS(D$2:D2)<=$D$1,INDEX(A$1:A$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<"",$C$1:$C$3)) ,$A$1:$A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")

Select both D2 and E2 then copy down as needed.


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
given :

A B
1 tree a 10
2 tree b 20
3 tree b 30
4 axe a 40
5 axe b 50
6 axe c 60

my search string cells are : c1 : c3

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

in cell D2 i entered :
{=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$7;SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF(C$1:C$3<"",C$1:C$3)),A$1 :A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")}
and i dragged down...


my problem is : WHAT FORMULA SHOULD I ENTER in cell E2 IN ORDER TO GET
THE
CORRESPONDING VALUES of "tree" and "axe" ???

i tried the following :

i enterd in E1 :SUMPRODUCT(COUNTIF(D2:D12,"*"))
and i entered in E1:
{=IF(ROWS(E$2:E2)<=E$1,INDEX(B$1:B$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($B$1:$B$7<"",$B$1:$B$7)) ,B$1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E2))),"")}
and i dragged down

now if i enter the word "axe" in cell C1 i get the following
WRONG
results

10 for axe a
20 for axe b
30 for axe c

HELP

T. Valko
TO mr t.valko...
 
Well, you've stumped me on that one! There's no way to do that using
formulas.

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
yes it worked ....THANK YOU

i have another issue.....please:

A B C D
1 a 10 2 c 40
2 b 20 3 d 50
3 b 30 4 e 60

I would like to use index(.(match)) function in cell B10 and that in
order
to be able to enter : a,b,.e,f in search string cell A10 ,So as to get
their corresponding results
how to do that ?? Or do you have other suggestions ??





"T. Valko" wrote:

The formula does work.

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
i get empty cells........there is something wrong







"T. Valko" wrote:

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

That formula needs to be array entered.

Enter this array formula in D2 and copy across to E2:

=IF(ROWS(D$2:D2)<=$D$1,INDEX(A$1:A$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<"",$C$1:$C$3)) ,$A$1:$A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")

Select both D2 and E2 then copy down as needed.


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
given :

A B
1 tree a 10
2 tree b 20
3 tree b 30
4 axe a 40
5 axe b 50
6 axe c 60

my search string cells are : c1 : c3

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

in cell D2 i entered :
{=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$7;SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF(C$1:C$3<"",C$1:C$3)),A$1 :A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")}
and i dragged down...


my problem is : WHAT FORMULA SHOULD I ENTER in cell E2 IN ORDER TO
GET
THE
CORRESPONDING VALUES of "tree" and "axe" ???

i tried the following :

i enterd in E1 :SUMPRODUCT(COUNTIF(D2:D12,"*"))
and i entered in E1:
{=IF(ROWS(E$2:E2)<=E$1,INDEX(B$1:B$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($B$1:$B$7<"",$B$1:$B$7)) ,B$1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E2))),"")}
and i dragged down

now if i enter the word "axe" in cell C1 i get the following
WRONG
results

10 for axe a
20 for axe b
30 for axe c

HELP

Pierre
TO mr t.valko...
 
okay ....THANKS anyway





"T. Valko" wrote:

Well, you've stumped me on that one! There's no way to do that using
formulas.

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
yes it worked ....THANK YOU

i have another issue.....please:

A B C D
1 a 10 2 c 40
2 b 20 3 d 50
3 b 30 4 e 60

I would like to use index(.(match)) function in cell B10 and that in
order
to be able to enter : a,b,.e,f in search string cell A10 ,So as to get
their corresponding results
how to do that ?? Or do you have other suggestions ??





"T. Valko" wrote:

The formula does work.

--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
i get empty cells........there is something wrong







"T. Valko" wrote:

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

That formula needs to be array entered.

Enter this array formula in D2 and copy across to E2:

=IF(ROWS(D$2:D2)<=$D$1,INDEX(A$1:A$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($C$1:$C$3<"",$C$1:$C$3)) ,$A$1:$A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")

Select both D2 and E2 then copy down as needed.


--
Biff
Microsoft Excel MVP


"pierre" wrote in message
...
given :

A B
1 tree a 10
2 tree b 20
3 tree b 30
4 axe a 40
5 axe b 50
6 axe c 60

my search string cells are : c1 : c3

in cell D1 i entered :
=SUMPRODUCT(COUNTIF(A1:A7,"*"&IF(C1:C3<"",C1:C3," skip")&"*"))

in cell D2 i entered :
{=IF(ROWS(D$2:D2)<=D$1,INDEX(A$1:A$7;SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF(C$1:C$3<"",C$1:C$3)),A$1 :A$7)),ROW(A$1:A$7)),ROWS(D$2:D2))),"")}
and i dragged down...


my problem is : WHAT FORMULA SHOULD I ENTER in cell E2 IN ORDER TO
GET
THE
CORRESPONDING VALUES of "tree" and "axe" ???

i tried the following :

i enterd in E1 :SUMPRODUCT(COUNTIF(D2:D12,"*"))
and i entered in E1:
{=IF(ROWS(E$2:E2)<=E$1,INDEX(B$1:B$7,SMALL(IF(ISNU MBER(SEARCH(TRANSPOSE(IF($B$1:$B$7<"",$B$1:$B$7)) ,B$1:B$7)),ROW(B$1:B$7)),ROWS(E$2:E2))),"")}
and i dragged down

now if i enter the word "axe" in cell C1 i get the following
WRONG
results

10 for axe a
20 for axe b
30 for axe c

HELP


All times are GMT +1. The time now is 07:04 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com