View Single Post
  #22   Report Post  
Posted to microsoft.public.excel.newusers
wilchong via OfficeKB.com wilchong via OfficeKB.com is offline
external usenet poster
 
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