If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Seeking Improvement on excel function
Dear T. Valko,
Thank, I know what is my problem. In order to make thing easy, I would like to break the whole thing into stages and explain step by step. Just before I talk about the complicate function, first of all, I would like to ask you one minor question regarding your previous function. Your previous suggested function as follow: =IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0), $E1),LOOKUP($E1,E$21:F$30), "-") I placed this formula in the cell of G1 and drag it from G1 to G10. Because the formula detects E2, so “G” will show in the cell of G2. “G” is come the database as follow: E21 = 11……………… F21 = T E22 = 12……………… F22 = G E23 = 13……………… F23 = R E24 = 14……………… F24 = E E25 = 15……………… F25 = K E26 = 16……………… F26 = Q E27 = 17……………… F27 = L E28 = 18……………… F28 = C E29 = 19……………… F29 = Z E30 = 20……………… F30 = I What I want to do the first thing is to revise the above formula slightly. The above formula will achieve the result based on the variable from E1 to E10, NOT from F1 to F10 as show you just now. So my first question is that do you think the argument of above function can be changed to F1 rather than E1? Many thanks for your time, Wilchong T. Valko wrote: I'm not following you on this. Post some sample data and explain what result you expect. Dear T. Valko, Thanks, your suggested function work very well, it is exactly fit to what [quoted text clipped - 49 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200908/1 |
#12
|
|||
|
|||
Seeking Improvement on excel function
The original formula in F1:F10 returns either a dash "-" or "OK".
Are you saying that you want another formula that checks F1:F10 for "OK" and if found do a lookup on the entries in E1:E10? If that's what yo want try this: =IF(F1="OK",LOOKUP(E1,E$21:F$30),"-") -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9ae88b0fb2b41@uwe... Dear T. Valko, Thank, I know what is my problem. In order to make thing easy, I would like to break the whole thing into stages and explain step by step. Just before I talk about the complicate function, first of all, I would like to ask you one minor question regarding your previous function. Your previous suggested function as follow: =IF(COUNTIF(INDEX(B$2:C$4,MATCH(D$1,A$2:A$4,0),0), $E1),LOOKUP($E1,E$21:F$30), "-") I placed this formula in the cell of G1 and drag it from G1 to G10. Because the formula detects E2, so "G" will show in the cell of G2. "G" is come the database as follow: E21 = 11...... F21 = T E22 = 12...... F22 = G E23 = 13...... F23 = R E24 = 14...... F24 = E E25 = 15...... F25 = K E26 = 16...... F26 = Q E27 = 17...... F27 = L E28 = 18...... F28 = C E29 = 19...... F29 = Z E30 = 20...... F30 = I What I want to do the first thing is to revise the above formula slightly. The above formula will achieve the result based on the variable from E1 to E10, NOT from F1 to F10 as show you just now. So my first question is that do you think the argument of above function can be changed to F1 rather than E1? Many thanks for your time, Wilchong T. Valko wrote: I'm not following you on this. Post some sample data and explain what result you expect. Dear T. Valko, Thanks, your suggested function work very well, it is exactly fit to what [quoted text clipped - 49 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200908/1 |
#13
|
|||
|
|||
Seeking Improvement on excel function
Dear T. Valko,
Thanks, your suggested function work very well. With your suggested formula, the “G” shown up in the of G2 when the formula detect “OK” in F2. OK, the first step is ok already. The second step is how to revise the formula in order to make the “G” shown up in the of G1 when the formula detect “OK” from F1 to F10. From my previous experience you show me, I can add “=IF(ROWS(G1:G$10)=SUM(--(COUNTIF (……………..” and then entered by "Shift + Control + Enter". I also dragged the function from G1 to G10. Of course, what I tried to do was failed. As a result, I need your advice. Many thanks for your time. Wilchong T. Valko wrote: The original formula in F1:F10 returns either a dash "-" or "OK". Are you saying that you want another formula that checks F1:F10 for "OK" and if found do a lookup on the entries in E1:E10? If that's what yo want try this: =IF(F1="OK",LOOKUP(E1,E$21:F$30),"-") Dear T. Valko, Thank, I know what is my problem. In order to make thing easy, I would [quoted text clipped - 45 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200908/1 |
#14
|
|||
|
|||
Seeking Improvement on excel function
OK, I think I uderstand what you want.
I would put all the table data in a contiguous range like this: ...........E..........F..........G 1.......11.........T..........- 2.......12.........G........OK 3.......13.........R..........- 4.......14.........E..........- Then: Formula in H1: =COUNTIF(G1:G10,"OK") Then this array formula** in H2 and copied down: =IF(ROWS(H$2:H2)H$1,"",INDEX(F$1:F$10,SMALL(IF(G$ 1:G$10="OK",ROW(F$1:F$10)),ROWS(H$2:H2))-ROW(F$1)+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9b0fa83429dd4@uwe... Dear T. Valko, Thanks, your suggested function work very well. With your suggested formula, the "G" shown up in the of G2 when the formula detect "OK" in F2. OK, the first step is ok already. The second step is how to revise the formula in order to make the "G" shown up in the of G1 when the formula detect "OK" from F1 to F10. From my previous experience you show me, I can add "=IF(ROWS(G1:G$10)=SUM(--(COUNTIF (......." and then entered by "Shift + Control + Enter". I also dragged the function from G1 to G10. Of course, what I tried to do was failed. As a result, I need your advice. Many thanks for your time. Wilchong T. Valko wrote: The original formula in F1:F10 returns either a dash "-" or "OK". Are you saying that you want another formula that checks F1:F10 for "OK" and if found do a lookup on the entries in E1:E10? If that's what yo want try this: =IF(F1="OK",LOOKUP(E1,E$21:F$30),"-") Dear T. Valko, Thank, I know what is my problem. In order to make thing easy, I would [quoted text clipped - 45 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200908/1 |
#15
|
|||
|
|||
Seeking Improvement on excel function
Dear T. Valko,
Yes, you got my idea. However, I want to make the formula even smarter. So, I tried to modify your suggested formula. After revised your formula, it should have two functions as follow: 1) Detect any “OK” in column F; 2) Once able to identify “OK” which refer to “12”, the array formula will base on the below database show “O” in the cell G1. The desire result is show below (of course, I failed to combine both functions in your array formula). RESULT:- ..........E..........F..........G 1.......11......... - ......... O 2.......12........OK........ - 3.......13......... - ......... - 4.......14......... - ......... - The revised formula is placed in G1 and drag it to G4. Please note that the revised formula is not necessary place in G1 and correspondent to column F, that is why I want to put an array formula. DATABASE:- “O” refers to “11”; “E” refers to “12”; etc. ….............E............F 21..……...11....……O 22……….12….……E 23……….13………O 24……….14………E 25……….15………O 26……….16………E 27……….17………O 28……….18………E 29……….19………O 30……....20…....…E Once again, many thanks for your advice, Wilchong T. Valko wrote: OK, I think I uderstand what you want. I would put all the table data in a contiguous range like this: ..........E..........F..........G 1.......11.........T..........- 2.......12.........G........OK 3.......13.........R..........- 4.......14.........E..........- Then: Formula in H1: =COUNTIF(G1:G10,"OK") Then this array formula** in H2 and copied down: =IF(ROWS(H$2:H2)H$1,"",INDEX(F$1:F$10,SMALL(IF(G $1:G$10="OK",ROW(F$1:F$10)),ROWS(H$2:H2))-ROW(F$1)+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Dear T. Valko, Thanks, your suggested function work very well. With your suggested [quoted text clipped - 30 lines] Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
#16
|
|||
|
|||
Seeking Improvement on excel function
Why would the result in G1 be O when 11 is not found in the table?
This is getting harder and harder to follow! -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9b195653cb2c7@uwe... Dear T. Valko, Yes, you got my idea. However, I want to make the formula even smarter. So, I tried to modify your suggested formula. After revised your formula, it should have two functions as follow: 1) Detect any "OK" in column F; 2) Once able to identify "OK" which refer to "12", the array formula will base on the below database show "O" in the cell G1. The desire result is show below (of course, I failed to combine both functions in your array formula). RESULT:- .........E..........F..........G 1.......11......... - ......... O 2.......12........OK........ - 3.......13......... - ......... - 4.......14......... - ......... - The revised formula is placed in G1 and drag it to G4. Please note that the revised formula is not necessary place in G1 and correspondent to column F, that is why I want to put an array formula. DATABASE:- "O" refers to "11"; "E" refers to "12"; etc. ..............E............F 21.......11......O 22....12....E 23....13...O 24....14...E 25....15...O 26....16...E 27....17...O 28....18...E 29....19...O 30......20......E Once again, many thanks for your advice, Wilchong T. Valko wrote: OK, I think I uderstand what you want. I would put all the table data in a contiguous range like this: ..........E..........F..........G 1.......11.........T..........- 2.......12.........G........OK 3.......13.........R..........- 4.......14.........E..........- Then: Formula in H1: =COUNTIF(G1:G10,"OK") Then this array formula** in H2 and copied down: =IF(ROWS(H$2:H2)H$1,"",INDEX(F$1:F$10,SMALL(IF( G$1:G$10="OK",ROW(F$1:F$10)),ROWS(H$2:H2))-ROW(F$1)+1)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. Dear T. Valko, Thanks, your suggested function work very well. With your suggested [quoted text clipped - 30 lines] Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
#17
|
|||
|
|||
Seeking Improvement on excel function
Dear T. Valko,
Sorry to make your life so difficult! Really sorry about that! Indeed, the formula which I wish to have is quite hard and challenging. The reason why I really need to place the array formula in G1 is because considering only a few “OK” (five out of 200 data) will be shown in column F. Furthermore, I will put this array formula in other spreadsheet at the end of the day. Finally, I want to save space as well. Actually, before asking your advice, I was trying very hard in fitting different database functions in array formula, of course, it failed. As a result, under desperate situation, I have to seeking the advice from the excel “guru” like you. Really appreciate your advice and time. Once again, many thanks for your advice, Wilchong T. Valko wrote: Why would the result in G1 be O when 11 is not found in the table? This is getting harder and harder to follow! Dear T. Valko, Yes, you got my idea. However, I want to make the formula even smarter. [quoted text clipped - 68 lines] Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
#18
|
|||
|
|||
Seeking Improvement on excel function
I don't see how O relates to 12 (OK)?
I thought you wanted the results that correspond to "OK"? At this point I'm lost! -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9b1b27e144142@uwe... Dear T. Valko, Sorry to make your life so difficult! Really sorry about that! Indeed, the formula which I wish to have is quite hard and challenging. The reason why I really need to place the array formula in G1 is because considering only a few "OK" (five out of 200 data) will be shown in column F. Furthermore, I will put this array formula in other spreadsheet at the end of the day. Finally, I want to save space as well. Actually, before asking your advice, I was trying very hard in fitting different database functions in array formula, of course, it failed. As a result, under desperate situation, I have to seeking the advice from the excel "guru" like you. Really appreciate your advice and time. Once again, many thanks for your advice, Wilchong T. Valko wrote: Why would the result in G1 be O when 11 is not found in the table? This is getting harder and harder to follow! Dear T. Valko, Yes, you got my idea. However, I want to make the formula even smarter. [quoted text clipped - 68 lines] Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
#19
|
|||
|
|||
Seeking Improvement on excel function
Really sorry Valko, may be thinking too much formulas has made me "hang over"
!! Yes, you are right. Should be "E", which relate to 12 (OK)! "O" is for 11, 13, 15 and etc! Many thanks, Wilson T. Valko wrote: I don't see how O relates to 12 (OK)? I thought you wanted the results that correspond to "OK"? At this point I'm lost! Dear T. Valko, Sorry to make your life so difficult! Really sorry about that! Indeed, [quoted text clipped - 26 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200908/1 |
#20
|
|||
|
|||
Seeking Improvement on excel function
Dear T. Valko,
Really sorry to make thing so complicated!, sorry about that! After considering your suggestion, I have changed the database in order to make the thing simple. One thing need your help, below is the database, I need a formula to extract all the data from A1 to A10. …...........A 1..……...TY 2………. - 3……….ER 4……….SX 5………. - 6………. - 7……….SX 8……….TY 9………. - 10……....ER The formula I wish to extract the data from A1 to A10 will show the result as follow: to list the data from B1 to B6: …...........B 1..…….. TY 2……… ER 3……… SX 4……… SX 5……… TY 6………. ER 7………. - 8………. - 9………. - 10……... - And again, from the previous experience you have shown me, I can use “=IF (ROWS(B$1:B10)=SUM(--(COUNTIF(…………… ” and then entered by "Shift + Control + Enter". I also dragged the formula from B1 to B10. Of course, what I tried to do was failed. As a result, I need your advice. Many thanks, Wilchong T. Valko wrote: I don't see how O relates to 12 (OK)? I thought you wanted the results that correspond to "OK"? At this point I'm lost! Dear T. Valko, Sorry to make your life so difficult! Really sorry about that! Indeed, [quoted text clipped - 26 lines] Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
Thread Tools | |
Display Modes | |
|
|