| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| mr t.valko ..help | Excel Discussion (Misc queries) | |||
| Thanks Pete_UK & T. Valko... | Excel Worksheet Functions | |||
Hybrid Mode
