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

T

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.
 
N

Narnimar

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

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,A1:D1,0)))

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

From:

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

To:

MATCH(H$1,A$1:D$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:D$1,0))<>"",ROW(Table)),ROWS(E$1:E1))-MIN(ROW(Table))+1,1),"")
 
N

Narnimar

Thank you so much!

T. Valko said:
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,A1:D1,0)))

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

From:

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

To:

MATCH(H$1,A$1:D$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:D$1,0))<>"",ROW(Table)),ROWS(E$1:E1))-MIN(ROW(Table))+1,1),"")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top