A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Seeking Improvement on excel function



 
 
Thread Tools Display Modes
  #21  
Old August 31st, 2009, 03:42 AM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old August 31st, 2009, 04:06 AM posted to microsoft.public.excel.newusers
wilchong via OfficeKB.com
external usenet poster
 
Posts: 94
Default 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  
Old August 31st, 2009, 04:29 AM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old September 4th, 2009, 01:34 AM posted to microsoft.public.excel.newusers
wilchong via OfficeKB.com
external usenet poster
 
Posts: 94
Default 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  
Old September 4th, 2009, 02:46 AM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old September 4th, 2009, 03:07 AM posted to microsoft.public.excel.newusers
wilchong via OfficeKB.com
external usenet poster
 
Posts: 94
Default 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  
Old September 4th, 2009, 03:42 AM posted to microsoft.public.excel.newusers
T. Valko
external usenet poster
 
Posts: 15,759
Default 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  
Old September 4th, 2009, 04:24 AM posted to microsoft.public.excel.newusers
wilchong via OfficeKB.com
external usenet poster
 
Posts: 94
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:07 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.