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 |
#21
|
|||
|
|||
Seeking Improvement on excel function
Try this array formula** entered in B1:
=IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"-"),"",INDEX(A$1:A$10,SMALL(IF(A$1:A$10"-",ROW(A$1:A$10)),ROWS(B$1:B1))-ROW(A$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. Copy down to B10. -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9b64d58adfe9c@uwe... 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 |
#22
|
|||
|
|||
Seeking Improvement on excel function
Thank Valko,
That is the array formula I looking for, however, how to re-adjust the array formula again if I want to have the result as following: …...........B 1..…….. TY 2……… ER 3……… SX 4……… SX 5……… TY 6………. ER 7………. - 8………. - 9………. - 10……... - Many thanks for your time, Wilchong T. Valko wrote: Try this array formula** entered in B1: =IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"-"),"",INDEX(A$1:A$10,SMALL(IF(A$1:A$10"-",ROW(A$1:A$10)),ROWS(B$1:B1))-ROW(A$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. Copy down to B10. Dear T. Valko, Really sorry to make thing so complicated!, sorry about that! After [quoted text clipped - 53 lines] Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
#23
|
|||
|
|||
Seeking Improvement on excel function
In ther words, you want a dash "-" instead of a blank...
Just change this portion: =IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"-"),"", To: =IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"-"),"-", -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9b662404072ff@uwe... Thank Valko, That is the array formula I looking for, however, how to re-adjust the array formula again if I want to have the result as following: ............B 1...... TY 2... ER 3... SX 4... SX 5... TY 6.... ER 7.... - 8.... - 9.... - 10..... - Many thanks for your time, Wilchong T. Valko wrote: Try this array formula** entered in B1: =IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"-"),"",INDEX(A$1:A$10,SMALL(IF(A$1:A$10"-",ROW(A$1:A$10)),ROWS(B$1:B1))-ROW(A$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. Copy down to B10. Dear T. Valko, Really sorry to make thing so complicated!, sorry about that! After [quoted text clipped - 53 lines] Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
#24
|
|||
|
|||
Seeking Improvement on excel function
Dear T. Valko,
Thanks for your suggested formula! Your formula is working very perfect. However, I have a minor problem which I put the same formula in different cell, the result turn out slightly differently despite how I revise the formula. First of all, let me explain the situation, below is the database. You will find a Greek symbol (alpha) in the cell of F10 as below. …...........F 5……… - 6……… - 7……… - 8……… - 9……… - 10…….. α 11……… - 12……... - 13………. - 14……... - OK, the second thing I did was put (entered by "Shift + Control + Enter") your suggest formula in the cell of F17 and drag the formula to F26. Below is the formula I put from the cell of F17 to F26: =IF(ROWS(F$17:F17)COUNTIF(F$5:F$14,"-"),"-",INDEX(F$5:F$14,SMALL(IF(F$5:F $14"",ROW(F$5:F$14)),ROWS(F$17:F17))-ROW(F$5)+1)) The result turns out not so perfect as before. The first result is ok, because the Greek symbol shown in the cell of F17, that is what I want, no problem about that. The second result should be show “-“ in the cell from F18 to F26. However, for the rest of cell (F18 to F26) shown #NUM!. At the beginning, I thought it is because it is a Greek symbol, so the formula cannot recognize it, but in fact it is not the case. I just want the formula show “-" in the cell from F18 to F26. Valko, I have spent a few day study the relationship between the formula and the data I set in the database, but still cannot work perfectly. I really cannot figure out the same formula can work perfectly in last example we discuss, but cannot work so nicely in above situation. Do you think because in my previous example, the database is started from the cell A, and the database this time started from F5, so the formula cannot work so perfectly. I really wish you can help out. Thanks, Wilchong 2009.09.04 T. Valko wrote: In ther words, you want a dash "-" instead of a blank... Just change this portion: =IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"-"),"", To: =IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"-"),"-", Thank Valko, That is the array formula I looking for, however, how to re-adjust the [quoted text clipped - 32 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200909/1 |
#25
|
|||
|
|||
Seeking Improvement on excel function
Below is the formula I put from the cell of F17 to F26:
=IF(ROWS(F$17:F17)COUNTIF(F$5:F$14,"-"),"-",INDEX(F$5:F$14,SMALL(IF(F$5:F $14"",ROW(F$5:F$14)),ROWS(F$17:F17))-ROW(F$5)+1)) That formula returns "-" in every cell. You have a slight typo in the SMALL(IF(F$5:F$14"" Should be: SMALL(IF(F$5:F$14"-" -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9b971b4fb2c54@uwe... Dear T. Valko, Thanks for your suggested formula! Your formula is working very perfect. However, I have a minor problem which I put the same formula in different cell, the result turn out slightly differently despite how I revise the formula. First of all, let me explain the situation, below is the database. You will find a Greek symbol (alpha) in the cell of F10 as below. ............F 5... - 6... - 7... - 8... - 9... - 10.... ? 11... - 12..... - 13.... - 14..... - OK, the second thing I did was put (entered by "Shift + Control + Enter") your suggest formula in the cell of F17 and drag the formula to F26. Below is the formula I put from the cell of F17 to F26: =IF(ROWS(F$17:F17)COUNTIF(F$5:F$14,"-"),"-",INDEX(F$5:F$14,SMALL(IF(F$5:F $14"",ROW(F$5:F$14)),ROWS(F$17:F17))-ROW(F$5)+1)) The result turns out not so perfect as before. The first result is ok, because the Greek symbol shown in the cell of F17, that is what I want, no problem about that. The second result should be show "-" in the cell from F18 to F26. However, for the rest of cell (F18 to F26) shown #NUM!. At the beginning, I thought it is because it is a Greek symbol, so the formula cannot recognize it, but in fact it is not the case. I just want the formula show "-" in the cell from F18 to F26. Valko, I have spent a few day study the relationship between the formula and the data I set in the database, but still cannot work perfectly. I really cannot figure out the same formula can work perfectly in last example we discuss, but cannot work so nicely in above situation. Do you think because in my previous example, the database is started from the cell A, and the database this time started from F5, so the formula cannot work so perfectly. I really wish you can help out. Thanks, Wilchong 2009.09.04 T. Valko wrote: In ther words, you want a dash "-" instead of a blank... Just change this portion: =IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"-"),"", To: =IF(ROWS(B$1:B1)COUNTIF(A$1:A$10,"-"),"-", Thank Valko, That is the array formula I looking for, however, how to re-adjust the [quoted text clipped - 32 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200909/1 |
#26
|
|||
|
|||
Seeking Improvement on excel function
Dear Valko,
Yes, you are right, I already tried that. But the result turns out the Greek Sign shown in the cell of F22. But I want the Greek Sign show in F17 and the rest of the cell show "-". Therefore, I revise your formula slightly! So I am thinking how to revise the formula in order to achieve show the Greek Sign in F17 and the rest of the cell show "-". Many thanks. Wilchong T. Valko wrote: Below is the formula I put from the cell of F17 to F26: =IF(ROWS(F$17:F17)COUNTIF(F$5:F$14,"-"),"-",INDEX(F$5:F$14,SMALL(IF(F$5:F $14"",ROW(F$5:F$14)),ROWS(F$17:F17))-ROW(F$5)+1)) That formula returns "-" in every cell. You have a slight typo in the SMALL(IF(F$5:F$14"" Should be: SMALL(IF(F$5:F$14"-" Dear T. Valko, Thanks for your suggested formula! Your formula is working very perfect. [quoted text clipped - 64 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200909/1 |
#27
|
|||
|
|||
Seeking Improvement on excel function
The formula works properly when I try it. (after making that change I
mentioned.) -- Biff Microsoft Excel MVP "wilchong via OfficeKB.com" u43231@uwe wrote in message news:9b97eb0f51c34@uwe... Dear Valko, Yes, you are right, I already tried that. But the result turns out the Greek Sign shown in the cell of F22. But I want the Greek Sign show in F17 and the rest of the cell show "-". Therefore, I revise your formula slightly! So I am thinking how to revise the formula in order to achieve show the Greek Sign in F17 and the rest of the cell show "-". Many thanks. Wilchong T. Valko wrote: Below is the formula I put from the cell of F17 to F26: =IF(ROWS(F$17:F17)COUNTIF(F$5:F$14,"-"),"-",INDEX(F$5:F$14,SMALL(IF(F$5:F $14"",ROW(F$5:F$14)),ROWS(F$17:F17))-ROW(F$5)+1)) That formula returns "-" in every cell. You have a slight typo in the SMALL(IF(F$5:F$14"" Should be: SMALL(IF(F$5:F$14"-" Dear T. Valko, Thanks for your suggested formula! Your formula is working very perfect. [quoted text clipped - 64 lines] Thanks for your advice, Wilchong -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...l-new/200909/1 |
#28
|
|||
|
|||
Seeking Improvement on excel function
OK, may be I change to another spreadsheet, I wish it will be ok this time!
Thanks, Wilchong T. Valko wrote: The formula works properly when I try it. (after making that change I mentioned.) Dear Valko, Yes, you are right, I already tried that. But the result turns out the [quoted text clipped - 26 lines] Thanks for your advice, Wilchong -- Message posted via http://www.officekb.com |
Thread Tools | |
Display Modes | |
|
|