Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #21   Report Post  
Old August 31st 09, 03:42 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
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" <[email protected] wrote in message
news:[email protected]
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   Report Post  
Old August 31st 09, 04:06 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 90
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   Report Post  
Old August 31st 09, 04:29 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
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" <[email protected] wrote in message
news:[email protected]
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   Report Post  
Old September 4th 09, 01:34 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 90
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   Report Post  
Old September 4th 09, 02:46 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
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" <[email protected] wrote in message
news:[email protected]
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   Report Post  
Old September 4th 09, 03:07 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 90
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   Report Post  
Old September 4th 09, 03:42 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2006
Posts: 15,768
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" <[email protected] wrote in message
news:[email protected]
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   Report Post  
Old September 4th 09, 04:24 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 90
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



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Suggested Improvement to Excel Filter Drop-Down THendr2929 Excel Discussion (Misc queries) 1 November 13th 08 06:38 PM
Excel novice seeking guidance Arch Excel Discussion (Misc queries) 1 October 13th 08 05:07 PM
Seeking an Excel guru in the Boston area - EXC102706 doc_rudolph Excel Discussion (Misc queries) 0 October 27th 06 01:59 PM
Excel Improvement Suggestion Carol Excel Worksheet Functions 1 January 25th 06 09:56 PM
Timer function needs improvement Mike K Excel Worksheet Functions 0 July 17th 05 05:35 AM


All times are GMT +1. The time now is 11:50 AM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017