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