Find the first & subsequent cells to contain text in a column & re

[T. Valko]

Im going to take a break for a few hours. When I return I'll put together a
sample file that does what you want.

 

[T. Valko]

Here's a small sample file that demonstrates this.

xNarnimar.xls 14kb

http://cjoint.com/?hAdCaKigG6

 

[Narnimar]

I reaized my mistake while copying webpage in the table. Excel is working
great now. Thank you so much T. Valko.

 

[T. Valko]

Good deal. Thanks for the feedback!

 

[Narnimar]

Dear T. Valko,
By the way, how to get the nonblank list of items from column A in E?

 

[T. Valko]

Ok, I assume you mean the items in column A that correspond to the non-blank
rows in Valx. Redefine the named range table to include column A:

Name: Table
Refers to: =$A$2:$D$7

Change the formula in I1 to:

=COUNTA(INDEX(Table,,MATCH(H1,A11,0)))

Change this expression in the formula in F1 (in 2 places):

From:

MATCH(H$1,B$1$1,0)

To:

MATCH(H$1,A$1$1,0)

Make sure you re-enter as an array!

And finally, enter this array formula in E1 and copy down:

=IF(ROWS(E$1:E1)<=I$1,INDEX(Table,SMALL(IF(INDEX(Table,,MATCH(H$1,A$1$1,0))<>"",ROW(Table)),ROWS(E$1:E1))-MIN(ROW(Table))+1,1),"")

 

[Narnimar]

Thank you so much!

Ok, I assume you mean the items in column A that correspond to the non-blank
rows in Valx. Redefine the named range table to include column A:

Name: Table
Refers to: =$A$2:$D$7

Change the formula in I1 to:

=COUNTA(INDEX(Table,,MATCH(H1,A11,0)))

Change this expression in the formula in F1 (in 2 places):

From:

MATCH(H$1,B$1$1,0)

To:

MATCH(H$1,A$1$1,0)

Make sure you re-enter as an array!

And finally, enter this array formula in E1 and copy down:

=IF(ROWS(E$1:E1)<=I$1,INDEX(Table,SMALL(IF(INDEX(Table,,MATCH(H$1,A$1$1,0))<>"",ROW(Table)),ROWS(E$1:E1))-MIN(ROW(Table))+1,1),"")

 

[T. Valko]

You're welcome!

--
Biff
Microsoft Excel MVP

Thank you so much!