Seeking Improvement on excel function

[T. Valko]

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

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

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

 

[wilchong via OfficeKB.com]

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

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]

 

[T. Valko]

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

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

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

 

[wilchong via OfficeKB.com]

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

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:

Thank Valko,
That is the array formula I looking for, however, how to re-adjust the

[quoted text clipped - 32 lines]

 

[T. Valko]

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 there:

SMALL(IF(F$5:F$14<>""

Should be:

SMALL(IF(F$5:F$14<>"-"

--
Biff
Microsoft Excel MVP

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

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:

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

 

[wilchong via OfficeKB.com]

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

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 there:

SMALL(IF(F$5:F$14<>""

Should be:

Dear T. Valko,
Thanks for your suggested formula! Your formula is working very perfect.

[quoted text clipped - 64 lines]

 

[T. Valko]

The formula works properly when I try it. (after making that change I
mentioned.)

--
Biff
Microsoft Excel MVP

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

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 there:

SMALL(IF(F$5:F$14<>""

Should be:

Dear T. Valko,
Thanks for your suggested formula! Your formula is working very perfect.

[quoted text clipped - 64 lines]

Thanks for your advice,
Wilchong

 

[wilchong via OfficeKB.com]

OK, may be I change to another spreadsheet, I wish it will be ok this time!
Thanks,
Wilchong

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]